# 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.  Register To Reply

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"))  Register To Reply

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.  Register To Reply

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?  Register To Reply

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?  Register To Reply

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?  Register To Reply

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.  Register To Reply

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*"))  Register To Reply

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)   Register To Reply

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)  Register To Reply