# How to Use ChatGPT with Excel to Write SUMIFS and XLOOKUP Formulas

> How to use ChatGPT with Excel: describe the formula in plain English, get SUMIFS and XLOOKUP explained, then check the total. Free hands-on lesson.

Source: https://learn.ynteractive.com/content/chatgpt-write-excel-formulas-from-plain-english · Updated 2026-11-17 · Free from Stepthrough (https://learn.ynteractive.com)

## Practice it step by step

[Start the free practice](https://learn.ynteractive.com/modules/chatgpt-excel-formula-plain-english)

[Take the free module: why it matters, practice and a quick check →](https://learn.ynteractive.com/courses/chatgpt-for-excel/1)

You do every step yourself in a practice copy of ChatGPT. It checks your work as you go, and “Do it for me” finishes any step you get stuck on. Free, about 9 minutes, and nothing touches your real accounts. [Open full screen](https://learn.ynteractive.com/modules/chatgpt-excel-formula-plain-english)

Quick answer

Open a [Temporary Chat](https://learn.ynteractive.com/content/glossary#temporary-chat) in ChatGPT and attach your spreadsheet export. Describe what you need in plain English, name the cell each formula goes in, ask for each part explained and say "don't calculate the answers yet". Then ask what the result should be and which rows it counted and left out, and paste the formula into your own workbook to check it.

Last updated November 17, 2026 · Last checked November 17, 2026

Free · Live now · [ChatGPT for Everyday Work](https://learn.ynteractive.com/courses/chatgpt-for-everyday-work) · [ChatGPT](https://learn.ynteractive.com/courses/chatgpt-for-everyday-work) · Excel · ~9 min · 19 steps · Beginner

Updated November 17, 2026. Built against the tool’s current screens and checked step by step. [How we check this lesson](https://learn.ynteractive.com/content/about#how-we-check)

You know what you want the spreadsheet to tell you, but not which function does it. In this free, interactive lesson you attach a freight broker's October shipments export to [ChatGPT](https://learn.ynteractive.com/courses/chatgpt-for-everyday-work), describe three formulas in plain English, read what each part does, and then make ChatGPT prove its answer on rows you can check by hand. The company and its loads are made up.

## At a glance

| Question | Answer |
| --- | --- |
| What you build | Attach your export, describe what you want in words, get each formula with every part explained, then make ChatGPT prove the total on rows you can check by hand. |
| Tools | ChatGPT |
| Time | About 9 minutes, 19 steps in 4 parts |
| Level | Beginner, no coding needed |
| Cost | Free |
| Ways to learn it | [Practice it step by step](https://learn.ynteractive.com/modules/chatgpt-excel-formula-plain-english) |

### The 4 parts, in short

1. Start from the real export: Look at the export, Turn on Temporary chat, Open the + menu, Add photos & files, and 2 more steps.
2. Describe it in plain English: Click the message box, Ask for three formulas, Send it.
3. Read each formula: Read the Total Cost formula, Read the SUMIFS formula, Read the XLOOKUP formula.
4. Make it prove the answer: Click the message box, Ask it to check its own work, Send it, Check the math by hand, and 3 more steps.

**Start here first:** [Summarize a Meeting Transcript with ChatGPT](https://learn.ynteractive.com/content/chatgpt-summarize-meeting-transcript) (module 1 of ChatGPT for Everyday Work)

**In the course:** [Part 3 · ChatGPT for Everyday Work](https://learn.ynteractive.com/courses/chatgpt-for-everyday-work) (Module 37 of the [full course](https://learn.ynteractive.com/content/course))

## The business case: the right total, without an hour on forums

Most office staff can describe the number they need in one sentence: "delivered cost for this carrier in October". Turning that sentence into SUMIFS or XLOOKUP is where the hour goes, searching forums and copying formulas that almost fit.

The bigger risk is a formula that runs but gives the wrong total. Nobody notices until the carrier [invoice](https://learn.ynteractive.com/content/glossary#invoice) doesn't match.

What changes: you describe the columns and the cells, ChatGPT writes the formulas and explains each part, and then you make it prove the answer on loads you can check yourself.

## The example: a dispatcher at a freight broker

A dispatcher at Harbor Line Freight (made up) has 14 October loads in a [CSV](https://learn.ynteractive.com/content/glossary/csv) export: Load ID, Ship Date, Carrier, Lane, Miles, Rate per Mile, Fuel Surcharge and Status. The boss wants the delivered October total for one carrier, a Total Cost per load, and a quick way to find a load's lane.

Download the file and follow along: [shipments-october.csv](/content/files/chatgpt-excel-formula-plain-english/shipments-october.csv).

## Who this lesson is for

Dispatchers, coordinators, office managers and anyone who lives in exported spreadsheets but never learned the function names. It is the first module of the [ChatGPT for [Excel](https://learn.ynteractive.com/content/tool/excel)](/courses/chatgpt-for-excel) course. If you want more Excel and Sheets with AI, see [AI for Excel and [Google Sheets](https://learn.ynteractive.com/courses/gemini-jump-start)](/courses/ai-for-excel-and-google-sheets).

## What ChatGPT produced in the real run

The first answer gave three formulas ready to paste, each with the cell and a one-line explanation: =E2*F2+G2 for Total Cost, a SUMIFS on carrier, status and an October date range, and =XLOOKUP(L4,A2:A15,D2:D15,"Not found"). It warned that the SUMIFS assumes Excel reads the ship dates as real dates, and that the ranges stop at row 15.

The second answer said L2 should show $4,950.70 for Summit Trucking from 5 loads, showed the math for each, and listed the 9 loads it left out with a reason (wrong carrier, in transit, or a November ship date). We checked the math by hand and it is right. It also warned that if Excel reads the dates as text, the SUMIFS can return $0, and that the formula filters by ship date because the export has no delivery date.

What we did not test: the formulas were not pasted into a real Excel workbook for this lesson. Paste them into your own copy and check that L2 shows $4,950.70.

## What this saves you

- Time: a few minutes instead of an hour searching for the right function.
- Accuracy: a total you checked on real rows, not one you hope is right.
- Next month: you know the two ways it breaks (text dates and short ranges) before it happens.
- What it does not replace: pasting the formula into your own workbook and checking it against a few rows.

## Gotchas

- Name the cells. "Put it in L2, the carrier is in L1" gets a formula you can paste.
- Say "don't calculate the answers yet", then ask it to check its own work in a second message. You get a formula and a proof.
- Ask which rows it counted and which it left out, with the reason. That is how you catch a wrong total.
- Dates in a CSV may open as text. Set one date cell to General: a five-digit number means Excel sees a real date.
- Fixed ranges like I2:I15 stop at the last row of this month. Extend them, or use whole columns, when the export grows.
- Shipment data is business data: use a Temporary Chat, or your company's approved AI tool.

## Next business case

Export too messy for formulas? [Clean up a messy spreadsheet export](https://learn.ynteractive.com/content/chatgpt-clean-messy-spreadsheet-export). Formulas already showing #N/A or #VALUE!? [Fix Excel formula errors](https://learn.ynteractive.com/content/chatgpt-fix-excel-formula-errors). Want ChatGPT to analyze the whole file? [Analyze a spreadsheet](https://learn.ynteractive.com/content/chatgpt-analyze-spreadsheet-csv).

## Try it yourself, free

Reading the steps is a start. Doing them is how it sticks. The interactive module walks you through every click in a practice copy of ChatGPT, checks each step, and never touches your real accounts.

[Start “How to Use ChatGPT with Excel to Write SUMIFS and XLOOKUP Formulas”](https://learn.ynteractive.com/modules/chatgpt-excel-formula-plain-english)

## Step by step: How to Use ChatGPT with Excel to Write SUMIFS and XLOOKUP Formulas

All 19 steps of the interactive module, in order. In the module you watch each one, then do it yourself in a practice copy that checks your work.

### 1. Start from the real export

1. **Look at the export.** Fourteen October loads. Note the columns: **Miles** in E, **Rate per Mile** in F, **Fuel Surcharge** in G, **Status** in H. Your boss wants the delivered cost per carrier. Read it, then click **✕**.
   Fourteen October loads. Miles are in column E, rate per mile in F, fuel surcharge in G, and status in H. Your boss wants the delivered cost for each carrier. Read it, then close the file.
2. **Turn on Temporary chat.** Carrier rates are business data. Click the **Temporary chat** icon at the top right so this chat stays out of your history.
   Carrier rates are business data.
3. **Open the + menu.** Click the **+** next to the message box.
4. **Add photos & files.** Click **Add photos & files**.
5. **Pick shipments-october.csv.** Pick **shipments-october.csv**.
   Pick the October shipments file.
6. **Open it.** Click **Open**. With the file attached, ChatGPT can see your real column letters.
   Now ChatGPT can see your real columns.

### 2. Describe it in plain English

7. **Click the message box.** You don’t need to know any function names. Say **what you want in words**, **which cell** each answer goes in, and ask for **each part explained**. Click the **message box**.
   You don't need to know any function names. Say what you want in plain words, which cell each answer goes in, and ask for each part explained.
8. **Ask for three formulas.** Paste in the request. It asks for a **Total Cost** per load, the **delivered October total** for one carrier, and a **lane lookup** by Load ID. It ends with **don’t calculate the answers yet**, so you can check them later.
   This asks for three formulas. A total cost for each load. The delivered October total for one carrier. And a lane lookup by load ID. It ends with, don't calculate the answers yet.
   ```text
   This is my October shipments export (attached). I'm a dispatcher, not an Excel expert, and I use Excel for Microsoft 365. I need three formulas: 1) a Total Cost for each load in column I: miles times rate per mile, plus the fuel surcharge. 2) In cell L2, the total cost of loads that were Delivered in October 2026 for the carrier typed in cell L1. 3) In cell L5, the lane for the Load ID typed in cell L4, and "Not found" if the ID isn't there. Give each formula ready to paste, say which cell it goes in, and explain each part in one short line. Don't calculate the answers for me yet.
   ```
9. **Send it.** Click **Send** (the arrow on the right).

### 3. Read each formula

10. **Read the Total Cost formula.** Miles times rate, plus fuel, in **I2**, then copied down to **I15**. One short line says what each cell does. Click the formula.
    Miles times rate, plus fuel. It goes in I two, and you copy it down to I fifteen. One short line says what each part does.
11. **Read the SUMIFS formula.** It adds Total Cost where the carrier matches **L1**, Status is **Delivered**, and the ship date is in October. Note the warning: it only works if Excel reads the dates **as dates**. Click the formula.
    The SUMIFS formula adds the total cost where the carrier matches L one, the status is Delivered, and the ship date is in October. Note the warning. It only works if Excel reads your dates as real dates.
12. **Read the XLOOKUP formula.** Type a Load ID in **L4** and the lane appears in **L5**, or **Not found**. Then the catch: all three formulas stop at **row 15**. Next month’s export will be longer, so the ranges must grow too. Click it.
    Or it says Not found. Then the catch. All three formulas stop at row fifteen. Next month's export will be longer, so the ranges must grow too.

### 4. Make it prove the answer

13. **Click the message box.** A formula you can’t check is a guess. Make ChatGPT **prove it** on rows you can check by hand. Click the **message box**.
    A formula you can't check is a guess. Make ChatGPT prove it, on rows you can check by hand.
14. **Ask it to check its own work.** Paste in the request. It asks what L2 should show for **Summit Trucking**, which loads it **counted and left out**, and **how each formula could break** next month.
    This asks what L two should show for Summit Trucking. Which loads it counted, which it left out, and why. And how each formula could break next month.
    ```text
    Now check your own work. With Summit Trucking in L1, what should L2 show? List the loads you counted and the ones you left out, with the reason, so I can check it by hand. Then tell me one way each formula could break if next month's export looks a little different.
    ```
15. **Send it.** Click **Send** (the arrow on the right).
16. **Check the math by hand.** It says **$4,950.70** from **5 loads**. Check one row: 382 × $2.45 = $935.90, plus $61 = **$996.90**. Then 355 × $2.60 = $923, plus $57 = **$980**. Three of the first and two of the second make $4,950.70. Click the total.
    It says four thousand nine hundred fifty dollars and seventy cents, from five loads. Check one row yourself. Three hundred eighty two miles at two forty five is nine hundred thirty five ninety. Add sixty one for fuel. That's nine hundred ninety six ninety. The Chicago loads come to nine hundred eighty. Three of the first and two of the second make the total.
17. **Check what it left out.** Nine loads left out, each with a reason. Find the two Summit loads in your file: **HL-2208** is still in transit, and **HL-2214** shipped November 2. Five counted plus nine left out is all 14. Click the list.
    Nine loads left out, each with a reason. Find the two Summit loads in your file. H L twenty two oh eight is still in transit. H L twenty two fourteen shipped in November. Five counted plus nine left out is all fourteen.
18. **See how each formula could break.** A column that moves, a longer export, extra spaces in a Load ID. The SUMIFS one is the silent kind: rows past **15** are just not counted, and the total still looks fine. Click the table.
    A column that moves. A longer export. Extra spaces in a load ID. The SUMIFS one is the quiet kind. Rows past fifteen are simply not counted, and the total still looks fine.
19. **Check the dates yourself.** If Excel reads the dates as text, SUMIFS shows **$0** instead of $4,950.70. Its test: set **B2** to **General**; a five-digit number means it is a real date. We did not paste these formulas into Excel for this lesson, so paste them into your copy and see if L2 shows **$4,950.70**. Click the note.
    If Excel reads the dates as text, the SUMIFS formula shows zero dollars. The test: change B two's format to General. A five digit number means it's a real date. We didn't paste these formulas into Excel for this lesson. You described the columns, got the formulas, and made ChatGPT prove them.

## Frequently asked questions

**How do I use Excel with AI?**

A simple way is to describe your columns and the answer you want to an AI chat like ChatGPT, then paste the formula it writes into your workbook. In this lesson ChatGPT wrote a SUMIFS and an XLOOKUP from a plain-English request and explained each part. Always check the result against a few rows by hand.

**Can ChatGPT write Excel formulas?**

Yes. Attach the file or describe the columns, say which cell the answer goes in, and ask for each part explained. In this lesson it wrote a SUMIFS and an XLOOKUP that matched the export's real column letters.

**How do I check a formula ChatGPT gives me?**

Ask what the answer should be for one case you can check, and which rows it counted and left out. Then add a few rows by hand and paste the formula into your own copy to see if it matches.

**Why does my SUMIFS return 0?**

A common cause is dates that Excel reads as text, so a date condition never matches. Change one date cell's format to General: if it turns into a five-digit number, it is a real date.

**What is the difference between SUMIFS and XLOOKUP?**

SUMIFS adds up the values that meet one or more conditions, like carrier and status. XLOOKUP finds one matching row and returns a value from it, like the lane for a Load ID.

**Can ChatGPT write formulas for Google Sheets?**

Yes, but say which app you use. This lesson asked for Excel for Microsoft 365, and the formulas were not tested in Google Sheets.

[Start this module free](https://learn.ynteractive.com/modules/chatgpt-excel-formula-plain-english)

- Previous in ChatGPT for Everyday Work: [ChatGPT: Explain a US Workplace Policy in Plain English](https://learn.ynteractive.com/content/chatgpt-explain-workplace-policy-plain-english)
- Next in ChatGPT for Everyday Work: [ChatGPT: Clean Up a Messy Spreadsheet Export Without Guessing](https://learn.ynteractive.com/content/chatgpt-clean-messy-spreadsheet-export)

## More in ChatGPT for Everyday Work

- [Summarize a Meeting Transcript with ChatGPT](https://learn.ynteractive.com/content/chatgpt-summarize-meeting-transcript): Paste a long meeting transcript into ChatGPT and get the decisions, an action items table with owners and due dates, and a follow-up email ready to send.
- [Reply Faster: Turn a Messy Customer Email Thread into a Clear, Friendly Reply](https://learn.ynteractive.com/content/chatgpt-reply-to-customer-emails): Turn a messy customer email thread into a clear, friendly reply: give ChatGPT the thread and your facts, check every promise, fix the tone, and send it from Gmail.
- [How to Create a Project in ChatGPT for Your Brand Voice and Files](https://learn.ynteractive.com/content/chatgpt-projects-brand-voice): Set up a ChatGPT Project with your brand voice instructions, your price list and your style guide, so every new chat already knows your business.
- [Analyze a Spreadsheet with ChatGPT: Upload a CSV, Get a Chart and Three Takeaways](https://learn.ynteractive.com/content/chatgpt-analyze-spreadsheet-csv): Upload a CSV of sales to ChatGPT, have it check the data first, ask your questions, get a chart you can download, and finish with the three takeaways that matter.
- [ChatGPT Work: Have ChatGPT Do a Multi-Step Task](https://learn.ynteractive.com/content/chatgpt-work-multi-step-task): Switch ChatGPT to Work, hand it a real job (compare three suppliers into a spreadsheet), approve the sites it opens, skip a login, answer its question, check the file…
- [Get a Morning Invoice Summary in Slack using ChatGPT Dots](https://learn.ynteractive.com/content/chatgpt-dots-invoice-summary-slack): Set up a ChatGPT dot to check Gmail for new invoice emails and post one summary to Slack every weekday morning. A 7-step practice based on OpenAI's published screens.

[All 28 modules in ChatGPT for Everyday Work](https://learn.ynteractive.com/courses/chatgpt-for-everyday-work)

## More ChatGPT tutorials

- [Get On-Brand Social Posts Every Time using a ChatGPT Custom GPT](https://learn.ynteractive.com/content/custom-gpt-social-media-assistant): See why generic prompts give generic results, then build a Custom GPT loaded with your brand voice and watch the output transform.
- [Prompt Writing 101: Role, Context, Task, Format](https://learn.ynteractive.com/content/prompt-writing-101-role-context-task-format): Ask ChatGPT for the same customer email twice, once vaguely and once with a four-part prompt, and see exactly why structure gets a better answer.
- [Send a Same-Day Follow-up After Every Discovery Call using ChatGPT](https://learn.ynteractive.com/content/chatgpt-discovery-call-follow-up-email-sales): Turn a discovery call transcript into the decisions, the next steps with owners and dates, deal notes for your CRM, and a follow-up email the prospect gets the same…
- [Hold Your Price When a Prospect Asks for a Discount using ChatGPT](https://learn.ynteractive.com/content/chatgpt-reply-to-discount-request-sales): A prospect wants 30% off and a free month to sign this week. Give ChatGPT the thread and your pricing rules, catch the discount you can't approve, and send a reply that…
- [Get Your Business Recommended by ChatGPT](https://learn.ynteractive.com/content/get-recommended-by-chatgpt): Ask ChatGPT who it recommends in your town, see which pages it trusts, then use it to write an FAQ page and Business Profile copy that answer real customer questions.
- [Run a Weekly Ops Review Where Every Task Has an Owner using ChatGPT](https://learn.ynteractive.com/content/chatgpt-weekly-operations-meeting-recap): Turn a weekly operations and vendor review into the decisions, the next steps with owners and dates, the risks to watch and a team recap, without turning a vendor's…

## Terms in this lesson

Plain-English definitions, each with an example from a free lesson.

[Invoice](https://learn.ynteractive.com/content/glossary#invoice) · [CSV](https://learn.ynteractive.com/content/glossary/csv) · [Temporary Chat](https://learn.ynteractive.com/content/glossary#temporary-chat) · [Follow-up prompt](https://learn.ynteractive.com/content/glossary#follow-up-prompt) · [File upload (in an AI chat)](https://learn.ynteractive.com/content/glossary/file-upload)

[The full AI glossary](https://learn.ynteractive.com/content/glossary)

## Browse related tutorials

[ChatGPT for Everyday Work (28)](https://learn.ynteractive.com/courses/chatgpt-for-everyday-work) · [ChatGPT tutorials (74)](https://learn.ynteractive.com/courses/chatgpt-for-everyday-work)
