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: 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! 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: 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. Author
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
