Count if with multiple criteria
 

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$37G$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$37G$52,G$3:G$ 37),0))}

Thanks for any help.

Count if with multiple criteria
 

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

This doesn't need to be array entered.

--
Regards,
Tom Ogilvy


"Chris" wrote in message
...
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$37G$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$37G$52,G$3:G$ 37),0))}

Thanks for any help.

Count if with multiple criteria
 

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$37G$52,1),0))

which is still an array formula

--
HTH

Bob Phillips

"Chris" wrote in message
...
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$37G$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$37G$52,G$3:G$ 37),0))}

Thanks for any help.

Count if with multiple criteria
 

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$37G$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$37G$ 52),0,(G$3:G$37G$52)),1),0))

Thanks.



"Bob Phillips" wrote:

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$37G$52,1),0))

which is still an array formula

--
HTH

Bob Phillips

"Chris" wrote in message
...
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$37G$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$37G$52,G$3:G$ 37),0))}

Thanks for any help.

Count if with multiple criteria
 

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?

--
HTH

Bob Phillips

"Chris" wrote in message
...
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$37G$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$37G$ 52),0,(G$3:G$37G$52)),1),
0))

Thanks.



"Bob Phillips" wrote:

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$37G$52,1),0))

which is still an array formula

--
HTH

Bob Phillips

"Chris" wrote in message
...
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$37G$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$37G$52,G$3:G$ 37),0))}

Thanks for any help.

Count if with multiple criteria
 

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 $7G$52),1),0))

again array entered, or

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

not array entered

--
HTH

Bob Phillips

"Chris" wrote in message
...
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$37G$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$37G$ 52),0,(G$3:G$37G$52)),1),
0))

Thanks.



"Bob Phillips" wrote:

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$37G$52,1),0))

which is still an array formula

--
HTH

Bob Phillips

"Chris" wrote in message
...
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$37G$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$37G$52,G$3:G$ 37),0))}

Thanks for any help.

Count if with multiple criteria
 

Thanks bob, you got it on the last post.

Chris

"Bob Phillips" wrote:

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 $7G$52),1),0))

again array entered, or

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

not array entered

--
HTH

Bob Phillips

"Chris" wrote in message
...
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$37G$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$37G$ 52),0,(G$3:G$37G$52)),1),
0))

Thanks.



"Bob Phillips" wrote:

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$37G$52,1),0))

which is still an array formula

--
HTH

Bob Phillips

"Chris" wrote in message
...
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$37G$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$37G$52,G$3:G$ 37),0))}

Thanks for any help.