Excel のピボットテーブルが合計ではなくデータの個数になる

Tested in: COUNT, COUNTA and SUM on a mixed range, and the eight-row sales totals, were evaluated in Google Sheets on 12 November 2026. Excel 365 menu names are the current desktop labels; I did not click through Value Field Settings in Excel this session.

If you do not have a pivot yet, start with How to Create a Pivot Table in Excel. If the totals are stale rather than counted, see pivot table not refreshing.

What a Count total looks like

On the eight-sale sample used elsewhere on this site, a Sum of Revenue is 24450, and East is 9450. A Count of the same field is 8 — one per row. If your “grand total” is a small whole number that matches the number of records, you are looking at a count, not at money.

In Google Sheets on 12 November 2026 I put 4800, 3000 and the text n/a in a three-cell range:

=COUNT(L1:L3)
=COUNTA(L1:L3)
=SUM(L1:L3)

Those returned 2, 3, and 7800. COUNT sees only the two numbers. COUNTA sees all three cells. SUM over the range added the two numbers and skipped the text, which is what you want for a revenue total — but a pivot that sniffed the text in that column may still have placed the field in Values as Count.

(If you write =SUM(4800,3000,"n/a") with the text as an argument rather than a cell, Google Sheets returns #VALUE!. The range form is the one that matches a source column.)

How to switch the field to Sum

In Excel 365

1. Click any number in the Values area, or click the field in the Values well.
2. Open Value Field Settings (from the field’s menu, or by right-clicking a value cell and choosing Value Field Settings).
3. Under Summarize value field by, choose Sum.
4. Click OK. The caption should change from “Count of Revenue” to “Sum of Revenue.”

In Google Sheets

1. Click the pivot to open the editor.
2. In Values, find the field.
3. Set Summarize by to SUM.

If the menu offers only Count, the field is being treated as text. Convert the source column to numbers first, then re-add the field; see convert text to numbers.

Clean the source so it stays on Sum

Look down the Revenue column (or whatever you are summing) for blanks, apostrophes in front of numbers, spaces, currency symbols that were pasted as text, and placeholders such as n/a or -. Replace placeholders with a real 0 if zero is the right amount, or leave the cell empty only if that row should not be in the list. Then refresh the pivot, or remove the field from Values and drag it back in.

A header that Excel misreads, or a leftover note in the first data row, can also push the field into Count. Keep a single header row and numbers only underneath.

Once Values says Sum, check it against the sheet: =SUM(F2:F9) should be 24450 on the sample, and =SUMIFS(F2:F9,B2:B9,"East") should be 9450. If the pivot is still at 8, you changed the wrong field, or a filter is hiding rows, or you are still on Count.

Do not format a Count as currency and hope it will start adding. The summary function, not the number format, decides whether the pivot counts or sums.