View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JE McGimpsey JE McGimpsey is offline
external usenet poster
 
Posts: 4,624
Default 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