SUMPRODUCT for conditional counting with OR logic
IntermediateIn real reporting, you often need to count records that match one condition or another, not just both at once. SUMPRODUCT can build that OR logic when a standard multi-criteria count falls short.
This sheet tracks employees by Department and Job level. Your goal: count every employee who works in Engineering or holds a Senior job level, counting overlap rows only once. The answer goes in B21.
How the arrays work
Compare a range to a value and Excel returns one result per cell, not a single answer. For example, =A2:A6="Yes" gives back something like {TRUE; FALSE; TRUE; ...}, one value per row.
SUMPRODUCT works with numbers, not with TRUE and FALSE, so you convert the array first. A double minus (--) in front of the array turns each TRUE into 1 and each FALSE into 0. SUMPRODUCT then adds up the 1s, which gives you a count.
Once both conditions are arrays of 1s and 0s, the math you put between them decides the logic:
- Multiply two arrays and the result holds a 1 only where both arrays had a 1, and a 0 everywhere else. That is AND.
- Add two arrays and every position where at least one array had a 1 turns non-zero: a 1 where just one matched, a 2 where both did. So non-zero marks the OR matches.
Your task
Review the employee table in A1:D16, then fill the summary cells:
- B18 employees in Engineering
- B19 employees at Senior level
- B20 employees who are both Engineering and Senior
- B21 employees who are Engineering or Senior, counted once (your main target)
B18 and B19 are single conditions. B20 and B21 each combine those two conditions, in different ways.
Need some help?
Hint 1
Start with the single-condition cells. For B18, compare the Department column (C2:C16) to its value, then put `--` in front to turn the TRUEs and FALSEs into 1s and 0s. SUMPRODUCT adds the 1s into a count. B19 works the same way on the Job level column (D2:D16).
Hint 2
For B21, add the two conditions with `+`. Overlap rows then land on 2. To get back to a clean count, compare the whole sum to zero (`(...)>0`) so every match becomes a single TRUE. Then turn that into 1s and 0s and total it, just like the other cells.
Related function(s)
SUMPRODUCT for conditional counting with OR logic
IntermediateIn real reporting, you often need to count records that match one condition or another, not just both at once. SUMPRODUCT can build that OR logic when a standard multi-criteria count falls short.
This sheet tracks employees by Department and Job level. Your goal: count every employee who works in Engineering or holds a Senior job level, counting overlap rows only once. The answer goes in B21.
How the arrays work
Compare a range to a value and Excel returns one result per cell, not a single answer. For example, =A2:A6="Yes" gives back something like {TRUE; FALSE; TRUE; ...}, one value per row.
SUMPRODUCT works with numbers, not with TRUE and FALSE, so you convert the array first. A double minus (--) in front of the array turns each TRUE into 1 and each FALSE into 0. SUMPRODUCT then adds up the 1s, which gives you a count.
Once both conditions are arrays of 1s and 0s, the math you put between them decides the logic:
- Multiply two arrays and the result holds a 1 only where both arrays had a 1, and a 0 everywhere else. That is AND.
- Add two arrays and every position where at least one array had a 1 turns non-zero: a 1 where just one matched, a 2 where both did. So non-zero marks the OR matches.
Your task
Review the employee table in A1:D16, then fill the summary cells:
- B18 employees in Engineering
- B19 employees at Senior level
- B20 employees who are both Engineering and Senior
- B21 employees who are Engineering or Senior, counted once (your main target)
B18 and B19 are single conditions. B20 and B21 each combine those two conditions, in different ways.
Need some help?
Hint 1
Start with the single-condition cells. For B18, compare the Department column (C2:C16) to its value, then put `--` in front to turn the TRUEs and FALSEs into 1s and 0s. SUMPRODUCT adds the 1s into a count. B19 works the same way on the Job level column (D2:D16).
Hint 2
For B21, add the two conditions with `+`. Overlap rows then land on 2. To get back to a clean count, compare the whole sum to zero (`(...)>0`) so every match becomes a single TRUE. Then turn that into 1s and 0s and total it, just like the other cells.