Multiple Criteria for CountIF
 

Hello, I was wanting to have a formula that counts all the cells that meet
two sets of criteria but the only way I know of doing this, shown below, does
not work. Can someone show me how I can do this properly?


=COUNTIF(AND(AA2:AA301="1",D2:D301="YES"))


Thank very much.

Multiple Criteria for CountIF
 

I do this with SUMPRODUCT

=SUMPRODUCT(--(AA2:AA301="1"),--(D2:D301="YES"))

--
HTH,
Barb Reinhardt



"Rob" wrote:

Hello, I was wanting to have a formula that counts all the cells that meet
two sets of criteria but the only way I know of doing this, shown below, does
not work. Can someone show me how I can do this properly?


=COUNTIF(AND(AA2:AA301="1",D2:D301="YES"))


Thank very much.

Multiple Criteria for CountIF
 

On Aug 1, 12:41*pm, Rob wrote:
Hello, I was wanting to have a formula that counts all the cells that meet
two sets of criteria but the only way I know of doing this, shown below, does
not work. *Can someone show me how I can do this properly?

=COUNTIF(AND(AA2:AA301="1",D2:D301="YES"))

Thank very much.

First off, you haven't actually defined your criteria in that formula,
you have only identified the range. COUNTIF(Range, Criteria), e.g.
COUNTIF(AA2:AA301,1). SUMPRODUCT is your solution.

=SUMPRODUCT(--(AA$2:A$301=1)*(D$1:D$301="yes"))

That should do it for you.

Steven

Multiple Criteria for CountIF
 

Just to add to Barb's response...

If those values in AA2:AA301 are really numbers (not text):
=SUMPRODUCT(--(AA2:AA301=1),--(D2:D301="YES"))



Barb Reinhardt wrote:

I do this with SUMPRODUCT

=SUMPRODUCT(--(AA2:AA301="1"),--(D2:D301="YES"))

--
HTH,
Barb Reinhardt

"Rob" wrote:

Hello, I was wanting to have a formula that counts all the cells that meet
two sets of criteria but the only way I know of doing this, shown below, does
not work. Can someone show me how I can do this properly?


=COUNTIF(AND(AA2:AA301="1",D2:D301="YES"))


Thank very much.

--

Dave Peterson

Multiple Criteria for CountIF
 

On Aug 1, 12:46*pm, Barb Reinhardt
wrote:
I do this with SUMPRODUCT

=SUMPRODUCT(--(AA2:AA301="1"),--(D2:D301="YES"))

--
HTH,
Barb Reinhardt

"Rob" wrote:
Hello, I was wanting to have a formula that counts all the cells that meet
two sets of criteria but the only way I know of doing this, shown below, does
not work. *Can someone show me how I can do this properly?

=COUNTIF(AND(AA2:AA301="1",D2:D301="YES"))

Thank very much.

Barb, you beat me to it. I talk too much.

Steven

Multiple Criteria for CountIF
 

Thanks for the multiple and quick responses but when I try any of them I
either get a 0 or #N/A as a result but it should come back with 11 for the
answer.

I did a direct Copy/Paste of both formula given.

Thanks again for helping, I hope to get this if possible.



" wrote:

On Aug 1, 12:46 pm, Barb Reinhardt
wrote:
I do this with SUMPRODUCT

=SUMPRODUCT(--(AA2:AA301="1"),--(D2:D301="YES"))

--
HTH,
Barb Reinhardt

"Rob" wrote:
Hello, I was wanting to have a formula that counts all the cells that meet
two sets of criteria but the only way I know of doing this, shown below, does
not work. Can someone show me how I can do this properly?

=COUNTIF(AND(AA2:AA301="1",D2:D301="YES"))

Thank very much.

Barb, you beat me to it. I talk too much.

Steven

Multiple Criteria for CountIF
 

Nevemind, I found the problem. Here's what it was.

Provided Formula:
=SUMPRODUCT(--(AA2:AA301="1"),--(D2:D301="YES")) Gives 0

Corrected:
=SUMPRODUCT(--(AA2:AA301=1),--(D2:D301="YES")) Gives 11


Again, I Thank You VERY much!

"Rob" wrote:

Thanks for the multiple and quick responses but when I try any of them I
either get a 0 or #N/A as a result but it should come back with 11 for the
answer.

I did a direct Copy/Paste of both formula given.

Thanks again for helping, I hope to get this if possible.



" wrote:

On Aug 1, 12:46 pm, Barb Reinhardt
wrote:
I do this with SUMPRODUCT

=SUMPRODUCT(--(AA2:AA301="1"),--(D2:D301="YES"))

--
HTH,
Barb Reinhardt

"Rob" wrote:
Hello, I was wanting to have a formula that counts all the cells that meet
two sets of criteria but the only way I know of doing this, shown below, does
not work. Can someone show me how I can do this properly?

=COUNTIF(AND(AA2:AA301="1",D2:D301="YES"))

Thank very much.

Barb, you beat me to it. I talk too much.

Steven

Multiple Criteria for CountIF
 

I'm guessing you have "N/A" in cells in one of the columns. If so, that
makes it even more fun! :)

Try this

=SUMPRODUCT(--(IF(ISNUMBER(AA2:AA301),AA2:AA301=1)),--(IF(ISTEXT(D2:D301),D2:D301="YES")))

Commit with CTRL SHIFT ENTER
--
HTH,
Barb Reinhardt



"Rob" wrote:

Thanks for the multiple and quick responses but when I try any of them I
either get a 0 or #N/A as a result but it should come back with 11 for the
answer.

I did a direct Copy/Paste of both formula given.

Thanks again for helping, I hope to get this if possible.



" wrote:

On Aug 1, 12:46 pm, Barb Reinhardt
wrote:
I do this with SUMPRODUCT

=SUMPRODUCT(--(AA2:AA301="1"),--(D2:D301="YES"))

--
HTH,
Barb Reinhardt

"Rob" wrote:
Hello, I was wanting to have a formula that counts all the cells that meet
two sets of criteria but the only way I know of doing this, shown below, does
not work. Can someone show me how I can do this properly?

=COUNTIF(AND(AA2:AA301="1",D2:D301="YES"))

Thank very much.

Barb, you beat me to it. I talk too much.

Steven