2

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
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.