SELECT & WHERE: Filter Rows with Flags and COUNTIFS

The SQL

-- rows: which orders match?
SELECT order_id, amount
FROM orders
WHERE region = 'North'
  AND amount >= 100;

-- one number: how many?
SELECT COUNT(*) FROM orders
WHERE region = 'North' AND amount >= 100;

SELECT SUM(amount) FROM orders WHERE status = 'Shipped';

SELECT SUM(amount) FROM orders
WHERE category IN ('Gear', 'Subscription');

The spreadsheet equivalent

• WHERE a AND b → a flag =IF(AND(…),1,0) or the criteria pairs of COUNTIFS / SUMIFS
• COUNT(*) → COUNTIFS(range1, crit1, range2, crit2)
• SUM(amount) WHERE … → SUMIFS(sum range, criteria range, criterion)
• >=, <>, LIKE 'B%' → ">="&G20, "<>Shipped", "B*" as criteria text
• IN (…) → one SUMIF/COUNTIF per value, added together