# Multi-conditional count with complex conditions

I have 4 columns and many rows. I need a formula for counting how many rows meet the following criteria:

1. contains a certain text in column A
2. contains a certain text in column B
3. contains a value of 0 in either column C or column D (if both are 0 on the same row that count as one result).

Example: count if column A is "2015", column B is "London", column C OR column D is 0

• If you can do it, add a helper column to the right. =if(or(a1=AVal,b1=BVal,c1=0,d1=0),1,0) Then just do a sum of the helper column and it will show how many rows match – bvaughn Dec 14 '16 at 17:02

You can use `SUMPRODUCT` to do conditional counts. While the formula is a little harder to understand than a `COUNTIFS`, it allows you way more flexibility in setting conditions. Here is a sample formula where the table is in A1:D100.

``````=SUMPRODUCT((A1:A100="textA")*(B1:B100="textB")*(C1:C100*D1:D100=0))
``````

Breaking it down:
`SUMPRODUCT` will sum an array of values. The array can be defined in the function and does not require special array entry.

Each factor in the expression represents a condition. If a condition is met, the value of the factor is 1. If it is not met, the factor is equal to 0. As a result, each product in the array will be equal to either 1 or 0. Those that are equal to 1 correspond to rows where all the conditions are met.

The conditions:
`(A1:A100="textA")` This is true (1) if the value of the cell in column A is the text "textA".

`(B1:B100="textB")` This is true (1) if the value of the cell in column B is the text "textB".

`(C1:C100*D1:D100=0)` This uses an arithmetic trick to do your OR comparison. The product of C and D will be 0 only if one of the two (or both) is equal to 0.

The sum of all these products is equal to your conditional count.