Spreadsheet formula builder for Excel and Google Sheets

You are a spreadsheet expert. Write a formula for me.

App and version: [e.g. "Excel for Microsoft 365", "Excel 2019", "Google Sheets", "LibreOffice Calc"]
Locale: [e.g. "English, comma as argument separator" or "Italian, semicolon as separator"]

My layout:
- Sheet name(s): [e.g. "Orders", "Prices"]
- Columns: [e.g. "A = Date, B = Customer, C = Product, D = Quantity, E = Unit price"]
- Data starts in row [N] and ends around row [N], or grows over time: [yes/no]
- Is the data in a formatted table? [yes/no, table name]

What I want: [IN PLAIN WORDS, e.g. "In G2, total revenue for the customer in F2, only for orders in 2026"]
Example: [A FEW ROWS AND THE RESULT YOU EXPECT]

Please give:
1. The formula, ready to paste into [CELL].
2. A plain-English explanation of each part.
3. A simpler alternative if one exists (or a version for older versions without dynamic arrays).
4. Edge cases that could break it: blank cells, text that looks like numbers, dates stored as text, duplicates, errors like #N/A, and how to handle them.
5. How to check the result by hand on one row.

Example (excerpt), Excel 365:
SUMIFS cannot multiply two columns, so use SUMPRODUCT:
=SUMPRODUCT((Orders!B2:B5000=F2)*(YEAR(Orders!A2:A5000)=2026)*Orders!D2:D5000*Orders!E2:E5000)

Explanation: each (condition) returns 1 or 0; multiplying keeps only rows for this customer in 2026, then multiplies quantity by price and adds them up.
Simpler alternative: add a Revenue column (=D2*E2) and use =SUMIFS(Orders!G:G, Orders!B:B, F2, Orders!A:A, ">="&DATE(2026,1,1), Orders!A:A, "<"&DATE(2027,1,1)).
Edge case: if dates are stored as text, YEAR() returns #VALUE!.

When to use it

Use this prompt when you know what a cell should calculate but not which functions to combine, when a formula someone else wrote returns errors, or when you need lookups, conditional sums or date logic. It is aimed at anyone who works in spreadsheets, from office staff to finance teams, and requires no programming knowledge.

How to customize it

• [APP AND VERSION] is important. Functions such as XLOOKUP, FILTER and LET exist only in newer versions of Excel, and some Google Sheets functions have no Excel equivalent.
• [LOCALE] decides whether arguments are separated by commas or semicolons, and whether function names are translated.
• [LAYOUT] lets the model write a formula with the right references instead of placeholders you must edit.
• [FORMATTED TABLE] allows structured references like Orders[Quantity], which keep working as data grows.
• [WHAT I WANT] and [EXAMPLE] together remove ambiguity: an expected result for one case is the best specification.

Why it works

Formulas fail most often because of the layout, not the logic: wrong ranges, mixed data types, or a function missing from your version. Giving the exact app, locale and column map means the model can write a formula you can paste directly.

The explanation turns the answer into something you can maintain, and the simpler alternative is often the better long-term choice, since a helper column is easier to audit than a clever one-line formula. The edge case list targets the problems that cause most real spreadsheet errors, such as dates stored as text. A manual check on one row is a quick way to catch a formula that runs but calculates the wrong thing.

Tips and variations

• Paste the formula into one cell first and check it against a value you calculated by hand.
• To understand an existing formula, paste it with “Explain this formula and suggest a clearer version.”
• Ask for conditional formatting rules or data validation in the same style.
• For repeated tasks across many files, ask whether a pivot table, Power Query or a script would be better.
• Remove names, salaries or customer details from examples before pasting them.