# COUNTIFS function not working

1. ## COUNTIFS function not working

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...

2. ## Re: COUNTIFS function not working

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")

3. ## Re: COUNTIFS function not working

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.

4. ## Re: COUNTIFS function not working

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:

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

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)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1