Most Excel courses list 'the 7 basic formulas' like it’s a holy scripture handed down from Redmond. They’re wrong. Not because the formulas are bad — but because they teach them in isolation, without context, and worse, without telling you which ones *don’t belong* on that list. COUNTA isn’t basic — it’s niche. CONCATENATE is obsolete. And if you’re still using IF without nesting it inside SUM or SUMIFS, you’re doing half the work.
The Setup
We’ll use a real dataset: Q1 sales leads from Alibaba’s regional partner program. It’s messy — names with extra spaces, inconsistent dates, blank rows, mixed currency symbols, and status entries typed in five different ways ('Won', 'won', 'WON ', 'Closed-Won', '✅ Won'). This isn’t fake data. It’s what lands in your inbox at 4:30 p.m. on a Thursday.
| A | B | C | D | E |
|---|---|---|---|---|
| Lead ID | Client | Value ($) | Date | Status |
| L-101 | BrightPath Ltd | $12,500 | 2024-01-12 | Won |
| L-102 | Nexus Global | $8,900 | 2024-01-18 | won |
| L-103 | Stellar Labs | $15,200 | 2024-02-03 | Closed-Won |
| L-104 | Acme Corp | $6,750 | 2024-02-14 | ✅ Won |
| L-105 | Orion Group | $11,300 | 2024-02-22 | Lost |
| L-106 | Veridian Inc | $9,800 | 2024-03-01 | Pending |
| L-107 | TerraFusion | $13,600 | 2024-03-08 | WON |
| L-108 | Skyline Partners | $7,200 | 2024-03-15 | Won |
The Challenge
You need to calculate total won revenue, count how many deals closed in February, and flag any client name with more than one space. That sounds simple — until you realize Excel sees 'WON ' and 'Won' as different values, treats '$12,500' as text (not a number), and reads '2024-02-03' as text unless you force date recognition. Also, the blank row between L-104 and L-105? It breaks most auto-fill ranges. You can’t just slap SUM(A2:A10) and call it done.
Here’s what we’ll actually use — the *real* 7 basic formulas that handle this mess:
- SUMIFS (not SUM — because we need conditions)
- COUNTIFS (not COUNTA — because we need logic)
- TEXTJOIN (replaces CONCATENATE and handles blanks cleanly)
- TRIM (fixes spacing before anything else)
- VALUE (converts '$12,500' into usable numbers)
- DATEVALUE (turns '2024-02-03' into a real date serial)
- IF + ISNUMBER + SEARCH (yes — this combo counts as *one* basic pattern)
Walking Through It
We’ll clean and analyze columns step-by-step. Start by selecting A1:E10 (your raw data). Then press Alt + H + F + I — that’s Excel’s shortcut for 'Format as Table'. Say yes to headers. Now your range becomes a structured table named Table1 — and formulas will auto-expand when you add rows later. (Trust me, I learned this the hard way after rebuilding a dashboard three times.)
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | In F2, enter =TRIM(B2) |
'Stellar Labs' (no trailing space) | Ctrl+C / Ctrl+V |
| 2 | In G2, enter =VALUE(SUBSTITUTE(C2,"$","") |
12500 (number, not text) | Alt+H+V+V (Paste Values) |
| 3 | In H2, enter =DATEVALUE(D2) |
45302 (Excel’s serial for 2024-01-12) | Ctrl+1 → 'Date' format |
| 4 | In I2, enter =IF(OR(ISNUMBER(SEARCH(" ",B2)),ISNUMBER(SEARCH(" ",F2))),"Double Space","OK") |
'Double Space' only for Stellar Labs (note two spaces in 'Stellar Labs ') | F2 → Ctrl+D (fill down) |
Now apply those formulas down to row 10. Copy F2:I2, select F3:I10, then press Ctrl+V. Done. No dragging. (That’s faster — and less error-prone.)
The Result
With cleaned columns, we get actionable outputs. In cell K1, type 'Total Won Revenue'. In K2, enter:=SUMIFS(G2:G10,I2:I10,"OK",E2:E10,"*Won*")
This sums column G only where the name is clean (I2:I10 = "OK") AND status contains 'Won' (case-insensitive wildcard).
In L1, type 'Feb Wins'. In L2:=COUNTIFS(H2:H10,">="&DATE(2024,2,1),H2:H10,"<="&DATE(2024,2,29),E2:E10,"*Won*")
| Lead ID | Clean Client | Value (num) | Date (serial) | Status | Space Check |
|---|---|---|---|---|---|
| L-101 | BrightPath Ltd | 12500 | 45302 | Won | OK |
| L-102 | Nexus Global | 8900 | 45308 | won | OK |
| L-103 | Stellar Labs | 15200 | 45313 | Closed-Won | Double Space |
| L-104 | Acme Corp | 6750 | 45324 | ✅ Won | OK |
| L-105 | Orion Group | 11300 | 45332 | Lost | OK |
| L-106 | Veridian Inc | 9800 | 45340 | Pending | OK |
| L-107 | TerraFusion | 13600 | 45347 | WON | OK |
| L-108 | Skyline Partners | 7200 | 45354 | Won | OK |
Total Won Revenue = $57,900
Feb Wins = 2 (L-103 and L-104)
What Could Go Wrong
Here are three mistakes people make — every time — and how to spot them:
- Mistake 1: Using SUM instead of SUMIFS on filtered data — If you filter the table to show only 'Won' rows and use SUM(G2:G10), Excel still sums all rows — including hidden ones. The fix? Use SUBTOTAL(109,G2:G10) or stick with SUMIFS (which ignores hidden rows automatically).
- Mistake 2: Forgetting VALUE wraps SUBSTITUTE — Entering
=SUBSTITUTE(C2,"$","")alone gives '12500' as text. You’ll see it left-aligned, and SUM will ignore it. Always wrap in VALUE — or better, use NUMBERVALUE if your locale uses commas as decimals. - Mistake 3: Wildcards in COUNTIFS without quotes — Writing
=COUNTIFS(E2:E10,*Won*)throws #NAME?. It must be=COUNTIFS(E2:E10,"*Won*"). Those quotes matter — Excel treats unquoted asterisks as multiplication operators.
One last tip: if you want to *see* which formulas reference which cells, press Ctrl+[ while in a formula cell. Excel highlights all precedent cells — instantly showing you dependencies. Try it on K2 right now.
