What you see
- Steps. The formula broken into parts, innermost first, the order a spreadsheet works them out. Each step shows its value: a number, a text, a range of cells or a whole array.
- Function arguments by name. For each function, which part of your formula is the lookup value, the criteria range, the sum range, and what each one holds.
- The branch not taken. In IF and IFERROR, the part the formula skipped is marked, so you can see which way it went.
- Checks. Likely mistakes, from approximate VLOOKUPs to criteria in the wrong quotes, each with the fix.
Worked examples
Total units for one region in one month
=SUMIFS(D2:D9,A2:A9,"East",F2:F9,">="&DATE(2026,3,1),F2:F9,"<"&DATE(2026,4,1))The date conditions are text joined to a number: ">="&DATE(2026,3,1) becomes ">=46082", the date's serial number.
D2:D9→8 rows × 1 col: 12, 30, 45, 8, …
A range: 8 cells in one column.
A2:A9→8 rows × 1 col: "East", "West", "East", "North", …
A range: 8 cells in one column.
F2:F9→8 rows × 1 col: 1/5/2026, 1/12/2026, 2/3/2026, 2/17/2026, …
A range: 8 cells in one column.
DATE(2026,3,1)→3/1/2026 (46082)
DATE: Builds a date from parts.
">="&DATE(2026,3,1)→">=46082"
The & operator joins as text.
DATE(2026,4,1)→4/1/2026 (46113)
DATE: Builds a date from parts.
"<"&DATE(2026,4,1)→"<46113"
The & operator joins as text.
SUMIFS(D2:D9,A2:A9,"East",F2:F9,">="&DATE(2026,3,1),F2:F9,"<"&DATE(2026,4,1))→9
SUMIFS: Adds values that meet every condition.
- sum_range
D2:D9→8 rows × 1 col: 12, 30, 45, 8, … - criteria_range1
A2:A9→8 rows × 1 col: "East", "West", "East", "North", … - criteria1
"East" - criteria_range2
F2:F9→8 rows × 1 col: 1/5/2026, 1/12/2026, 2/3/2026, 2/17/2026, … - criteria2
">="&DATE(2026,3,1)→">=46082" - criteria_range3
F2:F9→8 rows × 1 col: 1/5/2026, 1/12/2026, 2/3/2026, 2/17/2026, … - criteria3
"<"&DATE(2026,4,1)→"<46113"
Look up a price with XLOOKUP
=XLOOKUP("Lamp",C2:C9,E2:E9,"Not found")XLOOKUP returns the first match from the top, and the fourth argument replaces #N/A when nothing matches.
C2:C9→8 rows × 1 col: "Desk", "Chair", "Lamp", "Desk", …
A range: 8 cells in one column.
E2:E9→8 rows × 1 col: $249.00, $89.50, $39.99, $249.00, …
A range: 8 cells in one column.
XLOOKUP("Lamp",C2:C9,E2:E9,"Not found")→39.99
XLOOKUP: Searches one range and returns the matching item from another.
- lookup_value
"Lamp" - lookup_array
C2:C9→8 rows × 1 col: "Desk", "Chair", "Lamp", "Desk", … - return_array
E2:E9→8 rows × 1 col: $249.00, $89.50, $39.99, $249.00, … - if_not_found
"Not found"
Two-way lookup with INDEX and MATCH
=INDEX(A2:F9,MATCH("Fay",B2:B9,0),MATCH("Units",A1:F1,0))Each MATCH turns a label into a position; INDEX takes the row and column positions and returns the cell where they meet.
A2:F9→8 rows × 6 cols: "East", "Ana", "Desk", 12, …
A range: 8 rows × 6 columns.
B2:B9→8 rows × 1 col: "Ana", "Bo", "Cy", "Di", …
A range: 8 cells in one column.
MATCH("Fay",B2:B9,0)→6
MATCH: Position of a value in a list. 0 = exact match.
- lookup_value
"Fay" - lookup_array
B2:B9→8 rows × 1 col: "Ana", "Bo", "Cy", "Di", … - match_type
0
A1:F1→1 row × 6 cols: "Region", "Rep", "Product", "Units", …
A range: 6 cells in one row.
MATCH("Units",A1:F1,0)→4
MATCH: Position of a value in a list. 0 = exact match.
- lookup_value
"Units" - lookup_array
A1:F1→1 row × 6 cols: "Region", "Rep", "Product", "Units", … - match_type
0
INDEX(A2:F9,MATCH("Fay",B2:B9,0),MATCH("Units",A1:F1,0))→15
INDEX: Value at a given row and column of a range.
- array
A2:F9→8 rows × 6 cols: "East", "Ana", "Desk", 12, … - row_num
MATCH("Fay",B2:B9,0)→6 - column_num
MATCH("Units",A1:F1,0)→4
Clean up a name
=PROPER(TRIM(A3))Inner functions run first: TRIM removes the extra space, then PROPER fixes the capitals.
A3→"Bo Chen "
The value in A3.
TRIM(A3)→"Bo Chen"
TRIM: Removes extra spaces, leaving single spaces between words.
PROPER(TRIM(A3))→"Bo Chen"
PROPER: Capitalizes the first letter of each word.
Grade from a score with a lookup table
=XLOOKUP(B4,D2:D6,E2:E6,,-1)Match mode -1 finds an exact match or the next smaller value, so 64 lands on the 60 row: a D.
B4→64
The value in B4.
D2:D6→5 rows × 1 col: 0, 60, 70, 80, …
A range: 5 cells in one column.
E2:E6→5 rows × 1 col: "F", "D", "C", "B", …
A range: 5 cells in one column.
-1→-1
Makes the value negative.
XLOOKUP(B4,D2:D6,E2:E6,,-1)→"D"
XLOOKUP: Searches one range and returns the matching item from another.
- lookup_value
B4→64 - lookup_array
D2:D6→5 rows × 1 col: 0, 60, 70, 80, … - return_array
E2:E6→5 rows × 1 col: "F", "D", "C", "B", … - if_not_found
(omitted) - match_mode
-1→-1
Average salary in one department
=ROUND(AVERAGEIF(C2:C6,"Finance",D2:D6),0)AVERAGEIF averages only the rows whose department is Finance; ROUND tidies the result.
C2:C6→5 rows × 1 col: "Sales", "Finance", "Sales", "Ops", …
A range: 5 cells in one column.
D2:D6→5 rows × 1 col: $58,000, $72,000, $61,000, $66,500, …
A range: 5 cells in one column.
AVERAGEIF(C2:C6,"Finance",D2:D6)→76250
AVERAGEIF: Average of values that meet a condition.
- range
C2:C6→5 rows × 1 col: "Sales", "Finance", "Sales", "Ops", … - criteria
"Finance" - average_range
D2:D6→5 rows × 1 col: $58,000, $72,000, $61,000, $66,500, …
ROUND(AVERAGEIF(C2:C6,"Finance",D2:D6),0)→76250
ROUND: Rounds to a number of digits; negative digits round left of the decimal.
- number
AVERAGEIF(C2:C6,"Finance",D2:D6)→76250 - num_digits
0
Examples use the “Sales by region”, “People and emails” and “Scores and grades” tables in the data menu above.
What it checks
| Approximate matches | VLOOKUP, HLOOKUP and MATCH without an exact-match argument, which can return a wrong row with no error. |
|---|
| Ranges that will move | Lookup tables and criteria ranges without $, which slide out of place when the formula is filled down. |
|---|
| Numbers typed into formulas | Rates and thresholds that belong in their own cell. |
|---|
| Missing or misplaced quotes | Text criteria without quotes, cell references inside quotes like ">=E2", and numbers in quotes compared with numbers. |
|---|
| Errors swallowed whole | IFERROR around lookups where IFNA would catch only "not found". |
|---|
| Slow or fragile habits | Volatile functions like INDIRECT and OFFSET, whole columns in SUMPRODUCT, SUM(A1+A2), and IFs nested four deep. |
|---|
| The wrong app | Google Sheets functions in an Excel formula and the reverse, with the function to use instead. |
|---|
Questions
How is this different from Evaluate Formula in Excel?
Excel's Formulas > Evaluate Formula steps through one part at a time in a small dialog. The explainer shows every step at once with its value, explains each function's arguments by name, flags common mistakes, and works for Google Sheets formulas too.
Does my formula or data leave my browser?
No. The formula engine runs in your browser. Nothing you paste is sent anywhere, and pasted data isn't saved. Only the formula goes into the page address, so you can share a link to it.
Which functions does it calculate?
More than 150, covering lookups, logic, conditional totals, text, dates, dynamic arrays, finance, and Google Sheets functions like QUERY, SPLIT and REGEXEXTRACT. A function it doesn't calculate shows #NAME? in the steps, and the checks say so.
Why does a date show up as a number like 46082?
Spreadsheets store dates as day counts from 1900, and that number is what a formula works with. ">="&DATE(2026,3,1) becomes the text ">=46082", which SUMIFS then compares with each date.
Can I use my own data?
Yes. Choose "Paste my own" and paste cells copied from Excel or Google Sheets (or type CSV). They start at A1, the same as in your sheet, so the references in your formula point at the same cells.
Is it free?
Yes, with no sign-up.
Microsoft and Excel are trademarks of the Microsoft group of companies; Google Sheets is a trademark of Google LLC. The explainer is independent and calculates with our own formula engine.