Ignor null in count
 

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

Ignor null in count
 

One way (array-entered):

=SUM(IF(ISNUMBER(pcp),(ABS(pcp)<5.5)*(classificati on=$B$38)))


In article ,
Saintsman wrote:

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(classificat
ion=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

Ignor null in count
 

=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


--
Regards,

Peo Sjoblom



"Saintsman" wrote in message
...
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

Ignor null in count
 

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


--
Regards,

Peo Sjoblom

"Peo Sjoblom" wrote in message
...
=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


--
Regards,

Peo Sjoblom



"Saintsman" wrote in message
...
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

Ignor null in count
 

Thanks very much for this - made life much simpler for me

Saintsman

"Peo Sjoblom" wrote:

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


--
Regards,

Peo Sjoblom

"Peo Sjoblom" wrote in message
...
=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


--
Regards,

Peo Sjoblom



"Saintsman" wrote in message
...
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

Ignor null in count
 

Thanks for the feedback


--
Regards,

Peo Sjoblom



"Saintsman" wrote in message
...
Thanks very much for this - made life much simpler for me

Saintsman

"Peo Sjoblom" wrote:

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


--
Regards,

Peo Sjoblom

"Peo Sjoblom" wrote in message
...
=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


--
Regards,

Peo Sjoblom



"Saintsman" wrote in message
...
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