One problem that comes up a lot in Excel is counting or summing based on multiple OR conditions. For example, perhaps you need to analyze data and count orders in Seattle or Denver, for items that are Red, Blue, or Green? This can be surprisingly tricky, so naturally it makes a good challenge!
The challenge
The data below represents orders, one order per row. There are three separate challenges.
What formulas in F9, G9, and H9 will correctly count orders with the following conditions:
- F9 - Tshirt or Hoodie
- G9 - (Tshirt or Hoodie) and (Red, Blue, or Green)
- H9 - (Tshirt or Hoodie) and (Red, Blue, or Green) and (Denver or Seattle)
The green shading is applied with conditional formatting and indicates matching values for each set of OR criteria in each column.
For your convenience, the following named ranges are available:
item = B3:B16
color = C3:C16
city = D3:D16
The worksheet is attached. Leave your answers below as comments!
My solution uses SUMPRODUCT with ISNUMBER and MATCH like this:
=SUMPRODUCT(
ISNUMBER(MATCH(item,{"Tshirt","Hoodie"},0))*
ISNUMBER(MATCH(color,{"Red","Blue","Green"},0))*
ISNUMBER(MATCH(city,{"Denver","Seattle"},0))
)
Which will count orders where...
- Item is (Tshirt or Hoodie) and
- Color is (Red, Blue, or Green) and
- City is (Denver or Seattle)
Several people also suggested the same approach. I like this structure because it scales easily to handle more criteria, and also works with cell references (instead of hard-coded values). With cell references, the formula in H9 is:
=SUMPRODUCT(
ISNUMBER(MATCH(item,F3:F4,0))*
ISNUMBER(MATCH(color,G3:G5,0))*
ISNUMBER(MATCH(city,H3:H4,0))
)
The key to this formula is the ISNUMBER + MATCH construction. MATCH is setup "backwards" – lookup values come from the data, and criteria are used for the array. The result is a single column array each time MATCH is used. This array contains either #N/A errors (no match) or numbers (match), so ISNUMBER is used to convert to the boolean values TRUE and FALSE. The operation of multiplying the arrays together coerces the TRUE FALSE values to 1s and 0s, and the final array inside SUMPRODUCT contains 1s where rows meet criteria. SUMPRODUCT then sums the array and returns the result.
