Let AI do the totals instead of a pivot table

1. 1

   Check the data shape first

   However good the formula, the totals come out wrong if the source isn't in a shape that can be summarized. Microsoft's guidance for PivotTable data says the same: columns with a single header row, and no double header rows or merged cells.

   Text mixed into a number column fails more quietly. A PivotTable shows that field as a count instead of a sum, and Google Sheets' QUERY function treats values that don't match a column's main type as empty.

   The dates, places, products, quantities and amounts are invented practice data. Define what one output cell means before choosing sum or average. Code and tables below are editorial worked solutions.

   What to check before you summarize

   Check	Trouble looks like	Fix
   Header	Two header rows, or merged cells	One header row in row 1; unmerge
   One row per record	Subtotal or total rows in the middle	Delete them; totals belong in the summary
   Consistent names	"Seoul" and "Seoul City", stray spaces	Find and replace to one spelling
   Number format	Numbers stored as text, sitting on the left of the cell	Convert them (see "Write spreadsheet formulas with AI")

   Check it

   • Open the filter list on the region and product columns and look for near-duplicates such as "Seoul" and "Seoul City".

1. 2

   Describe rows, columns and values, then get a formula

   Rather than "make me a pivot table," describe the rows, columns and values: "regions down the side, products across the top, total revenue in the cells."

   

   And ask for a formula, not the numbers. Sums an assistant works out in the chat can be wrong (see long calculations in "Why AI gets things wrong"), while a formula in the sheet works from the source and recalculates when the source changes.

   The example sheet used in this guide (made-up October sales, rows 2–11)

   	A	B	C	D	E
   1	Date	Region	Product	Qty	Revenue
   2	2026-10-01	Seoul	Tumbler	12	180,000
   3	2026-10-01	Busan	Mug	20	180,000
   4	2026-10-02	Daegu	Tumbler	8	120,000
   5	2026-10-02	Seoul	Mug	15	135,000
   6	2026-10-05	Busan	Tumbler	10	150,000
   7	2026-10-05	Seoul	Tumbler	6	90,000
   8	2026-10-06	Daegu	Mug	12	108,000
   9	2026-10-07	Seoul	Mug	9	81,000
   10	2026-10-08	Busan	Mug	14	126,000
   11	2026-10-08	Daegu	Tumbler	5	75,000

   Here is my spreadsheet layout.
   Column A: Date, B: Region, C: Product, D: Qty, E: Revenue (number)
   Data runs from row 2 to row 11.
   I want a summary starting in cell G2, with regions as rows, products as columns and total revenue as the values. Give me the formula for Excel for Microsoft 365. Don't calculate any numbers yourself, just the formula.

   PIVOTBY groups and aggregates data in two directions at once. Put the formula below in G2 and it spills a table of revenue by region and product, with total rows and columns included.

   Only some Excel versions have PIVOTBY. If you get #NAME?, use the SUMIFS approach instead.

   fxCopy

   =PIVOTBY(B2:B11, C2:C11, E2:E11, SUM)

   B2:B11

   Row groups: the regions in column B

   C2:C11

   Column groups: the products in column C

   E2:E11

   Values to aggregate: revenue in column E

   SUM

   Add them up (AVERAGE or COUNT give averages or counts)

   Check it

   • If you pasted the formula exactly and still see #NAME?, your Excel doesn't have PIVOTBY. Move on to the SUMIFS method in the next step.
2. 3

   No PIVOTBY? Build a SUMIFS grid

   SUMIFS works in practically every version of Excel and in Google Sheets. Type the region and product names into an empty grid, fill the cells with SUMIFS, and you have the same summary.

   In Google Sheets you can also build it with a single QUERY formula, such as =QUERY(A1:E11, "select B, sum(E) group by B pivot C", 1).

   I get #NAME? when I use PIVOTBY, so I deleted it and typed Seoul, Busan and Daegu in G3:G5, and Tumbler and Mug in H2:I2. Give me one SUMIFS formula for H3 that I can fill across H3:I5. Just the formula, no calculations.

   Put this in H3 and fill it across H3:I5. Thanks to the $ signs, each cell picks up the region in its row and the product in its column. Add totals yourself, such as =SUM(H3:H5), and it all works the same in Google Sheets.

   fxCopy

   =SUMIFS($E$2:$E$11, $B$2:$B$11, $G3, $C$2:$C$11, H$2)

   $E$2:$E$11

   What to add: revenue, with row and column both fixed

   $B$2:$B$11, $G3

   Condition 1: the region matches column G; only the column is fixed

   $C$2:$C$11, H$2

   Condition 2: the product matches row 2; only the row is fixed

   Check it

   • After filling, click I5 and check that its formula reads =SUMIFS($E$2:$E$11, $B$2:$B$11, $G5, $C$2:$C$11, I$2). That's the Daegu and Mug cell.
3. 4

   Uploading a file? Ask for the code and the totals

   Some assistants can also run code on a file you upload, such as an Excel file or a CSV. Claude, for example, can analyze an uploaded CSV with code. Availability depends on the service, your plan and your organization's settings, and for company data, check your company's rules first.

   The numbers should then come from the code. Ask for the code it ran, the number of rows it read and the revenue total, so you have something to check against the source. The sales.csv below holds the same data as the example sheet in step 2.

   Use the sales.csv I uploaded to make a table of total revenue by region and product. Do the calculation by running code, and show me the code, the number of rows you read and the total revenue.

   The ten exercise rows total 1,245,000. The code and expected output below explain how to reproduce the grouping; they are not quoted logs from an AI tool run.

   If you have an execution environment, run the code and compare row count, source total and group totals. File headers must match the code.

   Example codeCopy

   import pandas as pd df = pd.read_csv("sales.csv") print(len(df), df["Revenue"].sum()) print(df.pivot_table(index="Region", columns="Product", values="Revenue", aggfunc="sum", margins=True))

   Revenue by region and product, as calculated by the code (example)

   Region	Mug	Tumbler	Total
   Busan	306,000	150,000	456,000
   Daegu	108,000	195,000	303,000
   Seoul	216,000	270,000	486,000
   Total	630,000	615,000	1,245,000

   Check it

   • Compare the row count (10) and total (1,245,000) with the source sheet's row count and =SUM(E2:E11). If they differ, check the uploaded file first.
4. 5

   Check against the source, then keep it reusable

   However you built the summary, run these four checks before you share it. Comparing totals catches missing rows; counting numbers catches values stored as text.

   For a weekly report, keep formulas or a PivotTable rather than pasting in the results. Formulas recalculate when the source changes, and a PivotTable picks up new data when you refresh it.

   Checks before you share (for the example sheet)

   Check	How	In this example
   Grand total	The summary's total equals =SUM(E2:E11) on the source	1,245,000
   Count of numbers	=COUNT(E2:E11) equals the number of data rows (text numbers make it smaller)	10
   One cell by hand	Pick a cell and add up its source rows yourself	Seoul, Tumbler: 180,000 + 90,000 = 270,000
   Split names	The summary has as many regions and products as you expect	3 regions, 2 products

   Check it

   • After pasting in next week's data, repeat the total and count checks. If the formula ranges (such as E2:E11) don't grow with the data, the new rows are left out.

This is a learning check, not a test you must pass. Use the explanations to understand the ideas.

1. Q1The grand total matches. Does that prove every region is grouped correctly?

   Write your thoughts (optional)

   Write your thoughts (optional)

   Your writing is not sent anywhere. It disappears when you leave or reload this page, so copy anything you want to keep.

   Read the answer and explanation

   No. Misclassified rows can preserve the total. Trace a group to source rows and inspect category spellings.

   I read and understood the explanation

   This is your own review, not an automatic score.

2. Q2Move the fictional 90,000 Seoul tumbler row to Busan. What are the two tumbler totals?

   Write your thoughts (optional)

   Write your thoughts (optional)

   Your writing is not sent anywhere. It disappears when you leave or reload this page, so copy anything you want to keep.

   Read the answer and explanation

   Seoul becomes 180,000 and Busan 240,000; the grand total is unchanged. These are invented practice amounts.

   I read and understood the explanation

   This is your own review, not an automatic score.

3. Q3A new sale is in row 12 but the formula ends at E11. What must change?

   Write your thoughts (optional)

   Write your thoughts (optional)

   Your writing is not sent anywhere. It disappears when you leave or reload this page, so copy anything you want to keep.

   Read the answer and explanation

   Extend corresponding value and criteria ranges together, then recheck row count and total. Do not expand only the amount range.

   I read and understood the explanation

   This is your own review, not an automatic score.

1. 6

   Try it: spot a bad grouping with a correct grand total

   A correct grand total can hide a wrong category. Moving one Seoul row into Busan preserves 1,245,000. The total is one check, not proof of correct grouping; trace an individual cell back to its rows.

   

   For practice, change the region of the 90,000 Seoul tumbler row to Busan. Seoul tumblers become 180,000 and Busan tumblers 240,000. Restoring the row yields 270,000 and 150,000. All figures are invented exercise data.

   When adding next week’s records, verify range expansion. A fixed range ending at row 11 omits later rows. Keep row count, data date and units beside the result. No external upload is required for this exercise.