Concept
1. The 4-step method
- Restate the problem and assumptions: "Sales are in column C, rows 2 to 100, region in B."
- Say the logic in words: "Count rows where region is North and amount is more than 50,000."
- Pick the function and write it with clear ranges.
- Test it aloud with one example row, and mention one edge case (blanks, not found, ties).
Interviewers care more about logic and communication than a missing bracket — but check brackets anyway.
2. Syntax checklist
- Every formula starts with
=. - Text in double quotes:
"North"; numbers without quotes. - Comparison in criteria is text:
">50000",">="&E1. - Count opening and closing brackets.
- Lock ranges you'll copy:
$C$2:$C$100. - Commas between arguments (some European Excel versions use semicolons — mention it if asked).
3. Ten classic problems
| # | Problem | Answer |
|---|---|---|
| 1 | Commission: 5% if sales ≥ 1,00,000; 3% if ≥ 50,000; else 1% | =IFS(B2>=100000, B2*5%, B2>=50000, B2*3%, TRUE, B2*1%) |
| 2 | Price of product code A2 from a Products table; show "Not found" | =XLOOKUP(A2, Products[Code], Products[Price], "Not found") |
| 3 | Count North orders above 50,000 | =COUNTIFS(B2:B100, "North", C2:C100, ">50000") |
| 4 | Domain from an email in A2 | =MID(A2, FIND("@", A2) + 1, 100) or =TEXTAFTER(A2, "@") |
| 5 | Age in completed years from DOB in B2 | =DATEDIF(B2, TODAY(), "y") |
| 6 | Rank sales in C2 among C2:C100 (highest = 1) | =RANK.EQ(C2, $C$2:$C$100, 0) |
| 7 | Running total in D | =SUM($C$2:C2) copied down |
| 8 | Second-highest sale | =LARGE(C2:C100, 2) |
| 9 | Number of distinct customers in A2:A100 | =COUNTA(UNIQUE(A2:A100)) (no blanks) |
| 10 | Value for name in H1 and month in H2 from a grid (names A2:A13, months B1:M1) | =INDEX(B2:M13, MATCH(H1, A2:A13, 0), MATCH(H2, B1:M1, 0)) |
4. Explaining an answer (example for #3)
"COUNTIFS counts rows that meet all conditions. First pair: region range B2:B100 equals "North". Second pair: amount range C2:C100 greater than 50,000 — written as the text ">50000". If the threshold were in a cell E1, I'd write ">"&E1. Blank rows don't affect it."
5. Older-Excel alternatives (in case they ask)
- No XLOOKUP →
=IFERROR(INDEX(Price, MATCH(A2, Code, 0)), "Not found") - No IFS → nested IF:
=IF(B2>=100000, B2*5%, IF(B2>=50000, B2*3%, B2*1%)) - No UNIQUE →
=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))(no blanks in the range) - No TEXTAFTER → MID/FIND as in #4.
6. Edge cases worth mentioning
Ties in RANK (RANK.EQ gives both the same rank); blanks in COUNTIF/UNIQUE; text numbers not counted by number criteria; exact vs approximate match in lookups.
Common mistakes
Writing silently without explaining. Quotes around numbers in maths (B2*"5%"). >50000 without quotes in COUNTIFS. Forgetting 0/FALSE for exact match in MATCH/VLOOKUP.