Multiple Columns, Multiple Criteria Count

Sorry if I don't word this perfectly, but I'm trying to count multiple options in multiple columns. The current formula I've attempted is:

=COUNTIFS('Data Dump'!AA:AA,{"Awaiting Copy","Awaiting Research"},'Data Dump'!AD:AD,{"Image Requested","Image Required"})

Within this, I want to count anything in column AA that equals Awaiting Copy or Awaiting Research, but must also contain Image Requested or Image Required in column AD.

Any guidance would be massively appreciated!

1 Answer

This seems to work in my test sheet (looking for ("a" or "b") in column A, and ("c" or "d") in column B)

``````=arrayformula(
sum(
(0+(((A:A="a")+(A:A="b")+0)>0))
*(0+(((B:B="c")+(B:B="d")+0)>0))
))
``````

Explanation: `A+A="a"` is true when there's an a in the cell, false when there isn't

`(A:A="a")+(A:A="b")+0`: adding zero converts true/false to integers, and they're added together (so this is zero if the cell isn't "a" or "b", 1 if it's either "a" or "b", and 2 if you're using different criteria that could both be true at once) - this uses the plus sign like an OR (we can't use OR because it would OR together the results from every cell)

`0+(...)>0` is 1 if the previous addition is greater than zero, otherwise it's zero (which turns any 2s from the previous line into 1s, not relevant for this exact case but for similar problems with different criteria)

`(...)*(...)` is 1 if both sets are 1, and 0 otherwise - so for each cell, we now have a value of 1 calculated if both criteria are true (so using * like an AND, again we can't use AND itself because it would AND together all the cells)

Then we sum, to add all the 1s together, and use arrayFormula because we're using non-array operators (=,+,*,>) on arrays

This approach (turn booleans into integers, do maths on them, get a result which is zero or 1, then sum) works for most things like this, but isn't very legible

• Hi Alex. This worked!! Thank you very much, this will save me a huge amount of time going forward.
– Ben
Commented Dec 7, 2021 at 14:10