Multiple If countifs

[sans]

Hello,

I'd like to use multiple if countifs, I tried a couple of formulas and even though the formulas work I keep getting the wrong results.

I've tried the following:

=IF(COUNTIFS(D4:E4,CHAR(251))+COUNTIFS(O4:P4,CHAR(252)),CHAR(251),CHAR(252))

=IF(AND(COUNTIFS(D4:E4,CHAR(251),O4:P4,CHAR(252))),CHAR(251),CHAR(252))


What's the problem with the above?

Thank you,
Sans

[Fotis1991]

Hi Sans

I use Excel 2003, so i can not tell you for countifs...

I am quite sure that you can do this with SUPRODUCT, as well.

So would you like to upload a sample workbook??

[Marcol]

From what I can see this doesn't need COUNTIFS(), COUNTIF() will do.
What it does need is the IF() condition qualified.

Please Login or Register  to view this content.

What are you really trying to do? CHAR(251) & CHAR(252) are pretty unusual qualifiers.

[sans]

Thank you both for your replies.

Actually the ranges I'd like to check are three. D4:E4, O4:P4 and Q4:Z4. The cells have either checkmarks or Xs.

I'd like to check, if in the first range all cells have Xs, the second range all checkmarks, and the third range all checkmarks, to get a checkmark otherwise get an X.

I'd like to use countifs or countif,or any other function that allows me to change the ranges very fast, that's why I avoided using sumproduct.

Thank you,

Spy

[NBVC]

Try:

=IF(AND(COUNTIF(D4:E4,CHAR(251))=2,COUNTIF(O4:P4.CHAR(252))=2,COUNTIF(Q4:Z4,CHAR(252))=10),CHAR(252),CHAR(251))

or

=IF(OR(COUNTIF(D4:E4,CHAR(252)),COUNTIF(O4:P4.CHAR(251)),COUNTIF(Q4:Z4,CHAR(251))),CHAR(251),CHAR(252))

which checks if either of the 3 ranges has at least one of the opposite symbol, then you get an X....otherwise a checkmark

[sans]

Thank you for the reply NBVC.

I tried both formulas and the second one works great. I don't understand though why this one doesn't seem to be giving the correct results.

=IF(AND(COUNTIF(D4:E4,CHAR(251)),COUNTIF(O4:P4,CHAR(252)),COUNTIF(Q4:Z4,CHAR(252))),CHAR(252),CHAR(251))

Thank you for the reply

[NBVC]

Your formula is checking that all 3 countifs return at least 1 match... so that means that only if there is at least one x in D4:E4, at least one check mark in O4:P4 and at least one check in Q4:Z4, then it will result in a checkmark... it does not check if all cells are marked with x's or check marks...

[sans]

Excellent, thank you for the explanation NBVC!

Sans