Get an Excel formula from plain English

Describe what you want to calculate and how your columns are laid out. Get a working Excel or Google Sheets formula, explained part by part.

How to use it

1. Describe your columns Which column holds what, and which row the data starts on. A screenshot of the top of the sheet helps.
2. Say what you want in plain words "Total sales for each rep in the North region last month" is better than "a SUMIF".
3. Test on the example Check the formula against the small test table first, then on your own data.
4. Ask for changes Different version of Excel, no XLOOKUP, need it to ignore blanks? Say so in the chat.

The prompt

You are a patient spreadsheet expert. Write a formula that does what I describe.

App and version (Excel 365, Excel 2016, Google Sheets): [SPREADSHEET_APP]

How my data is laid out (sheet names, columns, header row, where data starts) or see the attached screenshot: [DATA_LAYOUT_DESCRIPTION]

What I want to calculate, in plain words: [WHAT_I_WANT]

Where the formula goes: [TARGET_CELL]

Return:

- The formula, ready to paste, using my actual cell references.
- A part-by-part explanation in plain language.
- A version for the other app (Excel ↔ Google Sheets) if the syntax differs, and an alternative for older Excel if it uses newer functions such as XLOOKUP, FILTER or LET.
- A small example table (5–8 rows) with the expected result, so I can test it.
- Common ways it can go wrong with my data (numbers stored as text, dates as text, extra spaces, blank rows) and how to spot them.

Use absolute references ($) where the formula will be copied. Australian date format (DD/MM/YYYY) unless I say otherwise. If my description is ambiguous, state the assumption you made.

Example result

The formula (in Summary!B2, then copy down)

=SUMIFS(Sales!$D:$D, Sales!$C:$C, $A2, Sales!$B:$B, "North", Sales!$A:$A, ">="&DATE(2026,9,1), Sales!$A:$A, "<"&DATE(2026,10,1))

What each part does

• SUMIFS(Sales!$D:$D, …) — adds up the Amount column, but only for rows that pass every condition after it.
• Sales!$C:$C, $A2 — the Rep must match the name in column A of this row. $A2 locks the column but not the row, so it moves down when you copy.
• Sales!$B:$B, "North" — the Region must be North.
• Sales!$A:$A, ">="&DATE(2026,9,1) — the Date must be on or after 1 September 2026.
• Sales!$A:$A, "<"&DATE(2026,10,1) — and before 1 October, which covers all of 30 September without worrying about the last day.

Google Sheets: the same formula works as is.

Make the month easy to change: put any date in that month in Summary!E1 (e.g. 01/09/2026), then use:

=SUMIFS(Sales!$D:$D, Sales!$C:$C, $A2, Sales!$B:$B, "North", Sales!$A:$A, ">="&EOMONTH($E$1,-1)+1, Sales!$A:$A, "<="&EOMONTH($E$1,0))

Test table

Expected: Aroha = 1,500 (2 Sep + 30 Sep only); James = 2,000.

If the result looks wrong

• Result is 0: dates may be stored as text. Check with =ISNUMBER(Sales!A2) — it should say TRUE. Fix with Data → Text to Columns → Date: DMY.
• A rep is missing sales: extra spaces in names ("Aroha " ≠ "Aroha"). Clean with =TRIM().
• Amounts ignored: numbers stored as text show a green triangle in the corner; convert them to numbers.
• "North" vs "north": SUMIFS ignores case, so that's fine, but "Nth" won't match.