Details
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
Details
The Problem
We have a list of alphanumeric codes. Each code consists of a single letter (A, B, C, etc.) followed by a 3-digit number. These codes should appear in alphabetical order, but sometimes they are out of sequence. We want to flag out-of-sequence codes.
Challenge #1
What formula in the “Check” column will place an “x” next to a code that is out of sequence? In this challenge, we are only checking that the numeric portion of the code is out of sequence, not that the letter itself is out of sequence.
Challenge #2
How can the formula above be extended to check if “alpha” part of the code (A,B,C, etc.) is out of sequence? For example, we should flag a code that begins with “A” if it appears after a code that begins with “C” or “B”.
Download the worksheet below and take the challenge!
Note: there are 2 sheets in the workbook, one for Challenge #1, one for Challenge #2.
Hint - This video shows some tips for how to solve a problem like this.
Assumptions
- All codes always contain four characters: 1 uppercase letter + 3 numbers.
- The number of codes per letter is random, but there should be no gaps in numeric values.
- It is only necessary to mark the first code with a letter out of sequence, not all subsequent codes.
