COUNTIFS function not working

**d_mack** · 08-25-2010, 10:51 PM

Why won't this work? Trying to count cells that have "TEST" in column B and if it has a "Y" in the range from columns D to G....

=COUNTIFS(B2:B200,"TEST", D2:G200, "Y")

I need the second criteria to be across a range of columns where it equals "Y" and I get error this way. If I only look in one column (e.g. D) it works fine and returns the count.

Hoping someone can help. Thanks...

**knightcloud** · 08-25-2010, 11:37 PM

I think you need to separate each column and assign the corresponding values. I think that's the reason why the function "countifs" has many criteria so you could specify your parameters.

I'm not sure if this will help. I'm not an expert in excel but I hope this could help:

=COUNTIFS(B2:B200,"test",D2:D200,"y",E2:E200,"y",F2:F200,"y",G2:G200,"y")

**Norie** · 08-25-2010, 11:41 PM

Don't the criteria ranges all need to be the same size?

Your first range is 199 rows by 1 column, the second range 199 rows by 4 columns.

**DonkeyOte** · 08-26-2010, 02:25 AM

COUNTIFS is by definition an AND based test - so knightcloud's approach will return 1 wherein B is Test and each value in D:G is "Y".

If you wish to conduct an OR test - which I think you do - then one approach might be:

Please Login or Register  to view this content.

ORs in Arrays/SUMPRODUCTs are generally conducted by means of addition, ie:

Please Login or Register  to view this content.

the use of SIGN is to prevent double counting - ie should range2 and range4 contain x then count as 1 rather than 2
(you could use >0 test rather than SIGN if preferred)