Count if with multiple criteria

[Guest]

Hello,

I have read many of the posts on this site on this subject, but I am still
not able to get the formula to work. I have 2 columns that I am considering:

Column F and Column G My current attemt is:

{=SUM(IF($F$3:$F$37=$C59,IF(G$3:G$37>G$52,G$3:G$37),0))} This expression
seems to work to give me the correct sum. But instead of a sum, I would like
to use Count. when I try to replace the word Sum and use the word count, it
counts all of the rows. Not what I want to do. Here is a copy of how the
formula was in with count:

{=COUNT(IF($F$3:$F$37=$C59,IF(G$3:G$37>G$52,G$3:G$37),0))}

Thanks for any help.

 

[Tom Ogilvy]

=SUMProduct(--($F$3:$F$37=$C59),--(G$3:G$37>G$52))

This doesn't need to be array entered.

 

[Bob Phillips]

Using your formula, you would just sum a series of 1 instead of G4:G37

=SUM(IF($F$3:$F$37=$C59,IF(G$3:G$37>G$52,1),0))

which is still an array formula

 

[Guest]

Guys,

Thanks for the help, but unfortunatly it did not work. I have figured out
why it is not working so hopefully you can help me.

In the formula below: Cells in the range: g3:g37 are the result of the
following formula:

=IF(ISERROR(P18/Y18),"",(P18/Y18))


The problem is that many of the cells are resulting in the "" and not a true
numeric result. One solution is that I could replace the "" with a 0, but
then the standard deviation that I am trying to calculate and the averages I
am calculating are incorrect.

I thought of possible adding a isnumber to the formula, but have been
unsuccessful. Hopefully you guys have some thoughts.

=SUM(IF($F$3:$F$37=$C59,IF(G$3:G$37>G$52,1),0))

What about something like this: (It does not work but a thought)

=SUM(IF($F$3:$F$37=$C59,if(isnumber(IF(G$3:G$37>G$52),0,(G$3:G$37>G$52)),1),0))

Thanks.

 

[Bob Phillips]

I am struggling to see what problem you are getting as the data I am using
will add those "" ells as 0, which I presume is what you want.

Can you provide a simple example of P18, Y18, C59, G52 and F3:F37. Not every
row , 2 or 3 should be sufficient, with your expected result?

 

[Bob Phillips]

Okay, I've just got it.

Chris, try this

=SUM(IF($F$3:$F$7=$C59,IF(ISNUMBER(G$3:G$7)*(G$3:G$7>G$52),1),0))

again array entered, or

=SUMPRODUCT(--($F$3:$F$37=$C59),--(ISNUMBER(G$3:G$37)),--(G$3:G$37>G$52))

not array entered

 

[Guest]

Thanks bob, you got it on the last post.

Chris

Okay, I've just got it.

Chris, try this

=SUM(IF($F$3:$F$7=$C59,IF(ISNUMBER(G$3:G$7)*(G$3:G$7>G$52),1),0))

again array entered, or

=SUMPRODUCT(--($F$3:$F$37=$C59),--(ISNUMBER(G$3:G$37)),--(G$3:G$37>G$52))

not array entered