ignore cells that are blank
Try this (normally entered):
=SUMPRODUCT(--(J46:J522<""),--(J46:J522L3),--(J46:J522<L4))
--
Biff
Microsoft Excel MVP
"Robert" wrote in message
...
Hello, I am using the following formula to count the number of occurrences
of
my data being out of tolerance. My column of data (J46:J522) contains
numbers and blank cells. My upper and lower tolerances are contained in
cells L3 and L4. Therefore this equation is counting the number of data
results less than the lower tolerance and higher than the high tolerance.
This formula works great when L3 and L4 are bilateral (i.e. +/-.001). But
a
problem arises when L3 and L4 are unilateral (i.e. +.001/+.002). This is
because the formula is treating blank cells as 0.
Is there a way to make this formula ignore the blank cells?
{=SUM(IF((J46:J522<L4)+(J46:J522L3),1,0))}
|