Let AI do the totals instead of a pivot table

You don't need to know the PivotTable menus: describe the rows, columns and values, and the assistant writes the formula. The numbers come from the sheet or from code, never from the chat, and you check them against the source.

Step by step

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.

   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".
2. 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.
3. 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.
4. 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.

   I calculated this by running code. It read 10 rows, and total revenue is 1,245,000.

   Here's the code I ran. "Total" in the table is the All row and column in its output.

   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.
5. 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.