# COUNTIF: If A, B or C are true (Column A) and D is also true (Column B), then how many?

1. ## COUNTIF: If A, B or C are true (Column A) and D is also true (Column B), then how many?

I'm trying to create a function that will count how many if one of three criteria are met in Column A and one criteria is also met in Column B.

So essentially, column A holds 6 different criteria and Column B holds much more than that (it includes different products). I need to count for one product (column B) that is in either one of three phases of the life cycle (column A).

I have scoured the web, but have come up empty-handed. Any help would be greatly appreciated.

2. ## Re: COUNTIF: If A, B or C are true (Column A) and D is also true (Column B), then how many

Try using COUNTIFS with this syntax

=SUM(COUNTIFS(A:A,{"A","B","C"},B:B,"D"))

3. ## Re: COUNTIF: If A, B or C are true (Column A) and D is also true (Column B), then how many

Yea, I just tried =SUM(COUNTIFS(Data!I2:I60,{"Coding","Code Review","Testing"},Data!H2:H60,"DW"))

and I'm getting 0. I should be getting 4.

4. ## Re: COUNTIF: If A, B or C are true (Column A) and D is also true (Column B), then how many

That should work, doing the following

Count rows where
I2:I60 = "Coding" OR "Code Review" OR "Testing"
AND
H2:H60 = "DW"

Can you post a sample book?

5. ## Re: COUNTIF: If A, B or C are true (Column A) and D is also true (Column B), then how many

That formula works for me with test data - are you sure the cells in question exactly match those text values, no trailing/leading spaces or similar?

6. ## Re: COUNTIF: If A, B or C are true (Column A) and D is also true (Column B), then how many

Hard to evaluate without seeing a sample, but check for leading/trailing spaces maybe?

7. ## Re: COUNTIF: If A, B or C are true (Column A) and D is also true (Column B), then how many

I completely see what I did wrong. The "DW" criteria is only part of the full text of the cell. How do I have it look for cells that contain "DW" even if the rest of the text is different? Wildcard, essentially.

8. ## Re: COUNTIF: If A, B or C are true (Column A) and D is also true (Column B), then how many

Try

=SUM(COUNTIFS(Data!I2:I60,{"Coding","Code Review","Testing"},Data!H2:H60,"*DW*"))

9. ## Re: COUNTIF: If A, B or C are true (Column A) and D is also true (Column B), then how many

Worked like a charm! Thanks so much for the help! (I'm an idiot)

10. ## Re: COUNTIF: If A, B or C are true (Column A) and D is also true (Column B), then how many

You're welcome.

You're not an idiot, this is actually fairly advanced stuff.
Definately not the standard functional use of countifs (wouldn't find it in the help files)

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