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 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!