Multiple Criteria for CountIF

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

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.
 
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.
Click to expand...

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
 
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"))
 
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.
 
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!
 
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
 
Back
Top