Ignor null in count

S

Saintsman

I need to count the number of values between -5.5% & +5.5%.
I use named ranges pcp & classification (classification is linked to a drop
down list)

={SUM(IF(pcp<5.5%,IF(classification=B38,1,0)))-SUM(IF(pcp<-5.5%,IF(classification=B38,1,0)))}

The formula works nearly OK except that it counts NULL values. I can have 0
values ocasionally which need to be included, but not empty cells
Can anyone help?

Saintsman
 
J

JE McGimpsey

One way (array-entered):

=SUM(IF(ISNUMBER(pcp),(ABS(pcp)<5.5)*(classification=$B$38)))
 
P

Peo Sjoblom

=SUMPRODUCT(--(classification=B38),--(pcp<>""),--(pcp<5.5%),--(pcp>-5.5%))

btw, the less than character should be greater than >-5.5% in the second
part of your formula. The formula I gave you can be entered normally
 
P

Peo Sjoblom

Nevermind, your formula is of course correct except for the blanks, I didn't
even notice that you were subtracting the second string. Regardless even if
the formula is correct when it comes to finding values between 5.5% -5.5% it
doesn't really make any sense using 2 array formulas like that. SUMPRODUCT
is somewhat more efficient, if you would use an array formula you could have
written it like

=SUM((pcp<>"")*(pcp<5.5%)*(pcp>-5.5%)*(classification=B38))

or

=SUM(IF(pcp<>"",IF(pcp<5.5%,IF(pcp>-5.5%,IF(classification=B38,1,0)))))


both need to be array entered
 
Top