Level 2 · A filter you can check, written as a formula

Andrew wants a slice of the data: web orders, Kitchen or Decor. In Excel you’d usually click your way there with a filter. That’s fine for looking, but the moment someone asks “how many, and how much?”, you want the rule written down where the numbers can follow it. This level does both.

Filtering in Excel

Click any cell in the data and turn on Data → Filter (Ctrl+Shift+L; Google Sheets: Data → Create a filter). Each header gets a drop-down arrow:

• Untick Select All, tick web in the channel drop-down.
• In the category drop-down, tick Kitchen and Decor.

Ticks inside one column are OR (Kitchen or Decor). Filters on different columns are AND (web and one of those categories). The drop-downs also have Text Filters (Contains, Begins With), Number Filters (Greater Than, Between, Top 10) and Date Filters (Before, After, This Month) for rules a tick list can’t express. The status bar shows “9 of 40 records found”, and a filter icon on the header reminds you the view is filtered.

While you scroll a long list, keep the headers in view with View → Freeze Panes → Freeze Top Row. Freeze Panes on a selected cell freezes everything above and to the left of it, handy when the id column should stay visible too.

Advanced Filter: the rule written in cells

Data → Advanced (Sort & Filter group) takes a criteria range: header names copied above the conditions. Conditions on the same row are AND, conditions on separate rows are OR:

channel	category
web	Kitchen
web	Decor

That reads “web AND Kitchen, OR web AND Decor”. Advanced Filter can also copy the matching rows to another place. It doesn’t update by itself, though: change the data and you have to run it again.

The same rule as a formula

A criteria column turns the filter into a TRUE/FALSE per row. With the inputs in J1:J3:

G2   =AND(C2=$J$1,OR(E2=$J$2,E2=$J$3))

Row 4 is order 10949, web, a Thatcher Acacia Dutch Oven in Kitchen: C4="web" is TRUE, E4="Kitchen" is TRUE, so G4 is TRUE. Row 3 is a Solar Lantern bought in the app: the AND fails on the channel, so G3 is FALSE, whatever the category. Text comparisons with = ignore case, so "Web" would match too. The inputs are locked with $ because every row reads the same three cells.

Then the answers Andrew asked for:

J5   =COUNTIF(G2:G41,TRUE)
J6   =SUMIF(G2:G41,TRUE,F2:F41)

Change J1 to app and both numbers update. That’s the advantage over a ticked filter: the rule is visible, reusable, and checkable.

Filtered totals in Excel

A plain SUM(F2:F41) keeps adding hidden rows when a filter is on. Excel’s SUBTOTAL(109, F2:F41) (or AGGREGATE) adds only the visible ones, and the Total Row of an Excel Table uses it for you. The criteria column avoids the question altogether, because the filter lives in the formula.

Common mistakes

• Putting every test in one AND: a row can’t be Kitchen and Decor at once, so nothing matches.
• Using OR for everything: that keeps every web line plus Kitchen and Decor lines from any channel.
• Leaving J1 relative: filled down, it becomes J2, then J3, and each row tests a different input.
• Summing the visible rows of a filtered range with SUM and reporting it as the filtered total.