Lesson 3 of 9 · about 15 minutes

Pivot Tables: Answering Who, How Much and How Often

You'll be able to

Build a pivot table that totals and counts by one category
Add a second category to make a cross-tab
Use COUNTIFS and SUMIFS for two conditions at once
Group by month with a helper column
Check a pivot table's grand total against the raw data

With twelve payments you can eyeball the answer. With 48,000 rows of a real county checkbook you can't, and that is where the pivot table earns its odd name. It answers the questions reporters ask most: who got the money, how much, how often, and when.

We will keep using the Willow Bend County checkbook from the last lesson: columns A Date, B Vendor, C Department, D Amount, rows 2 through 13, total $260,505.

Two conditions with a formula first

Before pivots, one step up from COUNTIF. COUNTIFS and SUMIFS take as many range-and-condition pairs as you need.

Question	Formula	Result
Blue Ridge Fuel payments charged to Sheriff	`=COUNTIFS(B2:B13,"Blue Ridge Fuel",C2:C13,"Sheriff")`	3
Dollars for those payments	`=SUMIFS(D2:D13,B2:B13,"Blue Ridge Fuel",C2:C13,"Sheriff")`	20,345
Roads payments over $25,000	`=COUNTIFS(C2:C13,"Roads",D2:D13,">25000")`	3

Note the argument order. SUMIFS puts the range to add first. SUMIF, from the last lesson, puts it last. Nobody likes this. Everyone looks it up.

Formulas are fine for a few questions. When you want a total for every vendor at once, build a pivot table.

Building the pivot, step by step

Click any cell in the data. Every column needs a header in row 1, and there must be no blank rows or totals rows inside the data.
In LibreOffice Calc, choose Insert, Pivot Table, then Current selection. In Google Sheets, choose Insert, Pivot table, and pick New sheet.
Drag or add Vendor to Rows (LibreOffice calls this Row Fields).
Add Amount to Values (Data Fields). It defaults to Sum. Good.
Add Amount to Values a second time and change that one to Count.
Sort the result by the sum, largest first.

You get this:

Vendor	Payments	Total	Share of all spending
Acme Paving	3	182,350	70.0%
Harper Engineering	2	42,500	16.3%
Blue Ridge Fuel	4	29,665	11.4%
Valley Medical Supply	1	3,870	1.5%
Northline Office Supply	2	2,120	0.8%
Grand total	12	260,505	100.0%

The share column is a formula you add beside the pivot, each vendor total divided by the grand total with an absolute reference. Google Sheets can also show values as a percent of the grand total in the pivot editor.

Now you have a lead. One paving contractor received 70 percent of the dollars in three payments. That proves nothing about wrongdoing. It tells you which contract file to request first.

Check the grand total every time. If the pivot says $260,505 and SUM of column D says $260,505, you got every row. If they differ, stop and find out why before writing a word.

Swap the question

Take Vendor out of Rows and put Department in. Same data, new question.

Department	Payments	Total	Average payment
Roads	4	197,770	49,442.50
Parks	2	36,400	18,200.00
Sheriff	4	24,215	6,053.75
Clerk	2	2,120	1,060.00
Grand total	12	260,505	21,708.75

Check one row by hand. Roads: 48,200 + 112,750 + 9,320 + 27,500 = 197,770. Four payments. Average 197,770 divided by 4 = 49,442.50. Hand checks like this catch the day a filter was left on.

The cross-tab

Put Vendor in Rows and Department in Columns. Each cell is a total for that vendor in that department. Blank means no payments.

Vendor	Clerk	Parks	Roads	Sheriff	Total
Acme Paving		21,400	160,950		182,350
Blue Ridge Fuel			9,320	20,345	29,665
Harper Engineering		15,000	27,500		42,500
Northline Office Supply	2,120				2,120
Valley Medical Supply				3,870	3,870
Total	2,120	36,400	197,770	24,215	260,505

Cross-tabs find oddities. Why did a paving company bill the Parks department $21,400? Maybe a parking lot, which is perfectly normal. Maybe a road project charged to a fund with more money in it. You ask.

Grouping by month

Dates are too fine to group: every payment has its own day. Add a helper column E called Month with =TEXT(A2,"YYYY-MM"), copy it down, refresh the pivot's source range, and put Month in Rows.

Month	Total
2025-07	54,650
2025-08	113,930
2025-09	36,820
2025-10	10,885
2025-11	940
2025-12	36,400
2026-01	6,880
Grand total	260,505

August is big because of one payment. Paving is seasonal. A month-by-month chart of this would show a spike that a careless writer could call a "spending surge." The cause is a road crew doing summer work in summer.

The helper column only works if column A holds real dates. If the dates came in as text, TEXT returns the text unchanged or an error. The next lesson fixes that.

Settings that trip people up

Pivot tables do exactly what you tell them, which is the problem. A few defaults bite beginners.

Count of what. Count counts non-empty cells in the field you chose. Count of Amount and Count of Vendor match only if neither column has blanks. Pick a field that is never blank, like an ID or the amount.
Sum versus average. If Amount ever lands in Values as Count or Average without you noticing, the numbers will look plausible and be wrong. Read the label on the column header.
Page filters. Both programs let you filter the whole pivot (Google calls it Filters, LibreOffice calls it Page Fields or Filters). A filter left on from yesterday hides rows today. Glance at the filter area before trusting any total.
Source range. If you pasted 500 new rows under the old data, the pivot may still point at rows 2 through 13. Reset the range, or select whole columns when you build it.

One more habit: copy the finished pivot and paste it as values into a new sheet before you write from it. Then the numbers in your notes can't change when someone edits the source tab.

Example: the filter you forgot

Yesterday you filtered the department pivot to Sheriff to check fuel spending. Today you build the vendor pivot from a copy of that sheet and Acme Paving does not appear at all. The grand total reads $24,215, the Sheriff's total, instead of $260,505. The grand total check catches it in five seconds.

Reporting angle

Request the vendor payment register (checkbook) for at least two full fiscal years, with vendor, department, fund, account code, date, amount and check or invoice number.
Pivot by vendor and sort. Pull the contract or bid file for the top five.
Pivot by vendor and month. Look for payments that land just under a bidding or approval threshold, several in a row.
Ask the finance office whether the register includes payroll, refunds and transfers between funds. Those can swamp vendor totals.
Compare your department totals with the adopted budget and the audited financial report. Big gaps need an explanation.

Common mix-ups

Claim: A pivot table updates itself when you change the data. What the evidence shows: In LibreOffice and Excel you must refresh it, and if you added rows, extend the source range. Google Sheets updates automatically, but only within the range you gave it.
Claim: The top vendor on a list is the scandal. What the evidence shows: Top vendors are often the fuel supplier and the insurance pool. The pivot tells you where to look, and the documents tell you what happened.

Try it

Using your checkbook from the last lesson, build the vendor pivot with Sum and Count. Match the table above.
Confirm the grand total equals =SUM(D2:D13).
Rebuild it as a Vendor by Department cross-tab.
Add the Month helper column and pivot by month.
Write the COUNTIFS and SUMIFS for "Acme Paving, Roads" and check them against the cross-tab cell (expect 2 payments and $160,950).

Check yourself

1. In the Willow Bend checkbook pivot, Acme Paving received $182,350 of $260,505. What share is that?

2. What should a pivot table's grand total equal?

3. Which formula counts Blue Ridge Fuel payments charged to the Sheriff?

4. In Google Sheets, where do you start a pivot table?

5. Your pivot table shows 'Acme Paving' and 'ACME PAVING' as separate rows. What is the right fix?

Terms in this lesson

Pivot table: A spreadsheet tool that groups rows by one or more categories and counts, sums or averages each group.
Cross-tab: A table with one category down the side and another across the top, showing a total for each combination.
Helper column: An extra column you add to the data, such as month pulled out of a date, so you can group by it.
COUNTIFS / SUMIFS: Versions of COUNTIF and SUMIF that accept several conditions at once.

Full glossary

Sources

Google Docs Editors Help, Create and use pivot tables checked 2026-10
LibreOffice Help, Creating Pivot Tables checked 2026-10