Quick, clean, and to the point

Formula challenge - multiple OR criteria

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 will show correct counts based on multiple OR criteria?

What formulas in F9, G9, and H9 will correctly count orders with the following conditions:

  1. F9 - Tshirt or Hoodie
  2. G9 - (Tshirt or Hoodie) and (Red, Blue, or Green)
  3. 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!

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.