How it works

Describe what the cell should calculate in plain English, using your column letters and sheet names if you know them.
Pick Excel 365, older Excel or Google Sheets, optionally describe your columns, and click Write my formula.
Copy the formula into the cell it names, fill down if told to, and check the result against a few rows you can verify by hand.

Examples

Sample results for the inputs shown, so you can see the format and quality before you try it.

Example 1: input

What should the formula do? Look up the price for the product code in A2 from the Prices sheet (codes in column A, prices in column C). Show Not found if it's missing.

Spreadsheet app: Excel (Microsoft 365 or 2021+)

Example 1: output

Formula: =XLOOKUP(A2, Prices!$A:$A, Prices!$C:$C, "Not found") Put it in: the first row next to your product codes (for example B2), then fill down. How it works: - XLOOKUP searches for the code in A2 ... - ... in column A of the Prices sheet, anchored with $ so it doesn't shift when you fill down - It returns the value from the same row in column C (the price) - If the code isn't there, it shows Not found instead of #N/A Assumes: product codes are in column A and prices in column C of a sheet named Prices. If it doesn't work: Not found on codes you can see usually means extra spaces or codes stored as numbers on one sheet and text on the other. Try TRIM(A2) as the first argument, or retype the codes in one format. Older Excel version: =IFERROR(INDEX(Prices!$C:$C, MATCH(A2, Prices!$A:$A, 0)), "Not found")

Example 2: input

What should the formula do? Total the Amount for rows where Region is West and the Date is in the current month

Spreadsheet app: Google Sheets

Your sheet layout: Row 1 headers. A: Date, B: Region, C: Rep, D: Amount

Example 2: output

Formula: =SUMIFS(D:D, B:B, "West", A:A, ">="&EOMONTH(TODAY(), -1)+1, A:A, "<="&EOMONTH(TODAY(), 0)) Put it in: any empty cell outside columns A to D, for example F2. No need to fill down. How it works: - SUMIFS adds up column D (Amount) only for rows that meet every condition - B:B = "West" keeps West rows only - EOMONTH(TODAY(), -1)+1 is the first day of this month, so the first date test keeps dates on or after it - EOMONTH(TODAY(), 0) is the last day of this month, which caps the range Assumes: real dates in column A (not text), region names spelled exactly West, and numeric amounts in column D. If it doesn't work: if it returns 0, check that column A holds dates: select a cell and use Format > Number > Date. Dates pasted as text won't match.

Example 3: input

What should the formula do? Count how many unique customers are in B2:B500, ignoring blank cells

Spreadsheet app: Excel 2019 or older

Example 3: output

Formula: =SUMPRODUCT((B2:B500<>"")/COUNTIF(B2:B500, B2:B500&"")) Put it in: any single empty cell, for example D2. No fill needed. How it works: - COUNTIF(B2:B500, B2:B500&"") counts how many times each customer's name appears in the range - Dividing 1 by that count gives each copy a share, so a name listed three times adds up to exactly 1 - (B2:B500<>"") makes blank rows count as 0, and the &"" stops them causing a divide-by-zero error - SUMPRODUCT adds the shares, which equals the number of unique customers Assumes: customer names in B2:B500, with blanks allowed. If it doesn't work: slightly different spellings (Acme Inc vs Acme Inc.) count as different customers. Clean those first. On tens of thousands of rows this formula gets slow; a pivot table with a distinct count is faster.

Tips for better results

Use your real column letters. "Sum column D where column B is West" gets a formula you can paste; "sum the sales" gets one you have to edit.
Pick the right version. XLOOKUP, FILTER and UNIQUE don't exist in Excel 2019 and older, so a formula written for 365 shows #NAME? there.
If a formula returns 0 or Not found on data you can see, the usual cause is numbers or dates stored as text. Check the cell format first.
Test on five rows you can check in your head before you trust a total that covers five thousand.

FAQ

Is this Excel formula generator free?

Yes. Enter your email once to use it (you'll also get Something Big, our free weekly AI newsletter, and you can unsubscribe anytime). There's no account and no credit card.

Does it work for Google Sheets?

Yes. Pick Google Sheets and it writes a formula that works there, using Sheets functions such as ARRAYFORMULA, QUERY and REGEXMATCH when they fit.

What if my Excel doesn't have XLOOKUP?

Choose Excel 2019 or older and it uses INDEX/MATCH, SUMIFS, SUMPRODUCT and other functions every version has. When you pick Excel 365, it also adds an older-version fallback whenever it uses a newer function.

Why does my formula use commas when my Excel uses semicolons?

Many European locales separate arguments with semicolons. If Excel rejects the formula, replace each comma between arguments with a semicolon.

More free AI tools

Related prompts

Prefer to use ChatGPT or Claude directly? These free prompts do similar jobs.