Details
A long-time reader sent me an interesting problem this past week. The goal is to end up with a text string like “MWF” for Monday, Wednesday, Friday. The problem is that the weekdays are input as yes/no abbreviations like “NYNYNYN” for “MWF”.
Challenge
What formula will translate the “N” and “Y” to weekday abbreviations as shown in the screenshot above?
Extra points for style and elegance, but workhorse solutions are fine, too :)
Assumptions
- All inputs are 7 characters and contain only “Y” or “N”
- Days are mapped Sunday through Saturday, SMTWTFS.
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
