Why Your Pivot Table Shows Count Instead of Sum (And How to Fix It Permanently)

If you have ever dropped a Sales or Revenue field into a pivot table and watched it say “Count of Sales” instead of “Sum of Sales,” you already know the frustration. The number on screen is useless. You right-click, change it to Sum, and move on. Then the next time your data refreshes, the same thing happens again.

Most guides will tell you to fix the field setting and be done with it. That is the wrong answer. The field setting is a symptom. The root cause is almost always in your source data, and until you fix it there, you will keep fighting this battle every single time.

This article explains exactly why Excel defaults to Count, identifies the two causes that most tutorials miss (blank cells and text-formatted numbers from ERP exports), and then shows you the one-time Power Query setup that prevents the problem from ever coming back.

Why Excel Defaults to Count: The One Rule That Governs Everything

Excel applies a very simple rule when you drag a field into the Values area of a pivot table:

• If every single cell in that column contains a number, Excel defaults to Sum.
• If Excel detects that the field is not consistently numeric, because of text values, errors, formulas returning blanks, imported blank strings, or mixed data types, it often defaults to Countinstead of Sum.

One cell. That is all it takes to flip the entire column from Sum to Count. A dataset with 50,000 rows of clean revenue figures and a single empty cell in row 12,847 will produce “Count of Sales.”

This is not a bug. It is Excel trying to be helpful: if the column is not purely numeric, it has no way to know whether summing makes sense, so it falls back to counting. Knowing this rule is the key to understanding every fix that follows.

The Four Causes (Including the Two Most Guides Miss)

Cause 1: Blank Cells (The Most Common)

A blank cell anywhere in your values column is enough to trigger Count. This happens constantly with real-world data because:

• A transaction was recorded without an amount
• A row was imported from a system that leaves fields empty rather than writing zero
• Someone manually deleted a value and left the cell blank

Existing tutorials cover this. Most of them do not explain where those blanks come from. The answer is almost always your ERP or accounting system.

Cause 2: Text-Formatted Numbers from ERP Exports (The One Most Articles Miss)

This is the root cause that the majority of guides gloss over, and it is responsible for the majority of “Count of Sales” complaints from finance and operations teams.

When you export data from SAP, Oracle, Dynamics, NetSuite, QuickBooks, or virtually any ERP or accounting system, the export file is typically a CSV or a text-based format. Excel opens it, and the numbers look fine. They sit right-aligned in their cells, they have decimal points, everything seems in order.

But look closer. Select one of those “numbers” and check the cell format. In many ERP exports, the numbers are stored as text. Excel displays them as if they are numbers, but internally they are character strings. The pivot table sees a column of text and defaults to Count.

You can spot text-formatted numbers by two quick checks:

Check 1: Alignment. Genuine numbers are right-aligned in Excel by default. Text is left-aligned. If your “numbers” are sitting on the left side of the cell, they are text.

Check 2: The green triangle. Excel sometimes flags text-formatted numbers with a small green triangle in the top-left corner of the cell. If you see these, your column has text masquerading as numbers.

Check 3: ISNUMBER formula. In a blank cell next to your data, type =ISNUMBER(A2) where A2 is the first value in your column. If the result is FALSE for a cell that looks like a number, that number is stored as text.

Cause 3: Selecting Entire Columns as the Pivot Source

A less discussed but surprisingly common cause: full-column references increase the chance of hidden blanks, stale values, and data type inconsistencies entering the pivot cache. The pivot cache then includes hundreds of thousands of empty cells below your actual data, and those blanks trigger the Count default. Always use a defined table or a specific range, never an entire column.

If your source data is split across several sheets, see my guide on how to create a pivot table from multiple sheets in Excel. It explains the cleaner ways to consolidate data before building the pivot table.

Cause 4: Errors in the Values Column

Formula errors like #N/A, #VALUE!, or #DIV/0! in a values column behave exactly like text: one error flips the entire column to Count. Wrap any formulas that might produce errors in IFERROR(formula, 0) to keep them numeric.

The Quick Fixes (When You Need Results Now)

Before covering the permanent solution, here are the manual fixes for when you need to repair an existing dataset quickly.

Fix 1: Replace Blanks with Zero (Ctrl+H Method)

1. Select the entire column containing your values.
2. Press Ctrl+H to open Find and Replace.
3. Leave the Find What box completely empty.
4. Type 0 in the Replace With box.
5. Click Replace All.
6. Refresh your pivot table with Alt+F5.

Your pivot table should now default to Sum.

Caution: replacing blanks with zeros will affect average calculations. A zero is counted in an average; a blank is not. If your analysis includes averages, consider whether zero is the right replacement or whether those rows should be deleted entirely.

Fix 2: Convert Text-Formatted Numbers to Real Numbers

Method A: Paste Special Multiply

1. Type 1 in any empty cell and copy it.
2. Select the entire column of text-formatted numbers.
3. Right-click and choose Paste Special.
4. Select Multiply and click OK.

This forces Excel to perform a mathematical operation on each cell, converting the text to a true number in the process.

Method B: Text to Columns

1. Select the column of text-formatted numbers.
2. Go to Data > Text to Columns.
3. Click Finish immediately without changing anything.

Excel runs the column through its text parser and converts the values to real numbers. This is the fastest method for a single column.

Method C: VALUE Function in a Helper Column

Sometimes you should not modify the raw export.

For example:

• locked ERP exports
• shared finance reports
• audit-controlled files

In these cases, create a helper column using:

=VALUE(A2)

and build the pivot table from that cleaned helper field instead.

This keeps the original export untouched while giving the pivot table a fully numeric source.

Fix 3: Change the Field Setting Manually (Reactive, Not a Cure)

Right-click any value inside the pivot table > Summarize Values By > Sum. This changes the field setting for this pivot table only and does not fix the source data. The next time you create a pivot table from the same data, or refresh after a new import, the problem will return.

Use this only as a temporary measure while you fix the underlying data.

Fix 4: VBA Macro to Force Sum on All Fields at Once

If you have a pivot table with many value fields all defaulting to Count, this macro changes all of them to Sum in one run:

Sub SumAllValueFields()
Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet
Set ws = ActiveSheet
If ws.PivotTables.Count = 0 Then
MsgBox "No pivot table found on the active sheet.", vbExclamation
Exit Sub
End If
Set pt = ws.PivotTables(1)
Application.ScreenUpdating = False
pt.ManualUpdate = True
For Each pf In pt.DataFields
On Error Resume Next
pf.Function = xlSum
pf.Caption = Replace(pf.Caption, "Count of ", "Sum of ")
On Error GoTo 0
Next pf
pt.ManualUpdate = False
Application.ScreenUpdating = True
MsgBox "All value fields have been changed to Sum where supported.", vbInformation
End Sub

Note: This macro works for regular pivot tables. Some OLAP, Data Model, or calculated pivot fields may not allow the summary function to be changed by VBA, so the macro safely skips any field it cannot modify.

To use it: press Alt+F11 to open the VBA editor, insert a new module, paste this code, and press F5 to run it while your pivot table sheet is active.

This is useful for fixing existing pivot tables, but it is still a reactive fix. The moment you import new data, the problem can return.

The Permanent Solution: Power Query as Your Data Cleaning Layer

The reactive fixes above work, but they put the burden on you every time data is imported. The professional solution is to route all your data imports through Power Query and set the data types correctly once. After that, every refresh automatically produces clean, numeric data that pivot tables will always sum.

This is the approach that no other article covers in full, and it is the reason finance teams who have set it up properly never think about this problem again.

Step 1: Load Your Data Through Power Query Instead of Directly

Instead of opening a CSV or ERP export directly in Excel:

1. Go to Data > Get Data > From File > From Text/CSV (or From Workbook, From Database, depending on your source).
2. Select your export file.
3. In the preview window, click Transform Data instead of Load.

This opens the Power Query Editor, which is where the permanent fix happens.

Step 2: Set Column Data Types Explicitly

In the Power Query Editor, look at your values column. At the top of the column header, there is a small icon that shows the current data type (ABC for text, 123 for whole numbers, a decimal icon for decimals).

If your Sales or Revenue column shows ABC, it is text. Click that icon and change it to Decimal Number (or Whole Number if appropriate for your data).

Power Query will attempt to convert all values. If any values cannot be converted because they contain text, currency symbols, or special characters from the ERP export, they will show as errors. This is actually useful: it forces you to see exactly which rows have bad data, so you can fix them at the source or handle them with a replacement rule.

Step 3: Handle Unconvertible Values

If your ERP export includes values like “N/A,” “–,” or blank strings in the values column:

1. In the Power Query Editor, right-click the column header.
2. Choose Replace Values.
3. Replace the problematic text (e.g., “N/A”, “–”, “”) with 0 or null depending on your analysis needs.
4. Then change the data type to Decimal Number.

Step 4: Remove the Automatic “Changed Type” Step if Causing Issues

Power Query sometimes auto-inserts a “Changed Type” step based on the first 1,000 rows of data. If your data has inconsistencies that cause this step to produce errors, delete it from the Applied Steps pane on the right and re-apply the data type change manually after handling your replacements.

Step 5: Load to Table and Build Your Pivot Table from That Table

Once the data types are set correctly in Power Query:

1. Click Close and Load to load the cleaned data into an Excel table.
2. Build your pivot table from that Excel table, not from the raw import.

Now the workflow is:

• New ERP export arrives
• Paste it into the source file (or point the query at the updated file)
• Refresh the Power Query connection (Data > Refresh All)
• Pivot table refreshes with clean, numeric data
• Pivot table defaults to Sum automatically, every time

The data type rules you set in Power Query apply permanently to every refresh. You set it up once.

Using an Excel Table as Your Pivot Source (The Non-Power-Query Safety Net)

If Power Query is not an option in your environment, the next best thing is to ensure your pivot table’s source data is defined as an Excel Table (Insert > Table, or Ctrl+T).

A properly defined Excel Table:

• Dynamically expands as you add rows below it, so the pivot source never includes empty rows
• Helps prevent the “entire column” range mistake that adds blank cells to the pivot cache
• Makes refresh workflow easier

This does not fix text-formatted numbers, but it eliminates the third cause (over-large source ranges) and makes the data range management much cleaner.

Diagnosing Which Cause You Have: A Decision Flow

Use this sequence to find your specific problem:

Step 1. Select your values column and press Ctrl+G > Special > Blanks. If Excel selects any cells, you have blank cells. Fix them first.

Step 2. In a helper column, use =ISNUMBER(A2) for a few rows. If any return FALSE, you have text-formatted numbers.

Step 3. Check your pivot source range. Is it referencing full columns (A:G) instead of a defined table or specific range? If so, change the source to a proper range or Table.

Step 4. Look for the green error triangle on any cells in your values column. If present, right-click the column header, choose Format Cells, confirm it is set to Number, then use Text to Columns to force Excel to re-evaluate each cell’s type.

Once each of these checks comes back clean (no blanks, no text-formatted numbers, no oversized source range) your pivot table will default to Sum from that point forward.

Summary: What to Do and When

Situation	Best Fix
Quick one-time repair of blanks	Ctrl+H Find blank, Replace with 0
Quick one-time repair of text numbers	Text to Columns or Paste Special Multiply
Many value fields stuck on Count	VBA macro to force xlSum on all fields
Recurring ERP/CSV import workflow	Power Query with explicit data type steps
No Power Query, dynamic data	Format source as Excel Table, replace blanks on import

The pivot table’s Count vs. Sum decision is made the moment you drop a field into the Values area. By the time you are looking at “Count of Sales,” the problem is already in your source data. Fix it upstream and you will never need to fix it again.

Frequently Asked Questions

Why is my pivot table showing Count instead of Sum when there are no blank cells?

If you have confirmed there are no blank cells but the pivot table is still defaulting to Count, the most likely cause is text-formatted numbers. A cell can display a number while internally storing it as text, and there is no visual difference unless you know where to look. Run the =ISNUMBER() check on your values column. If any cell returns FALSE, that is your culprit. The second possibility is that your source range includes empty rows below your data, which happens when you select full columns (A:G) instead of a defined range or Table.

Is there a way to make pivot tables always default to Sum instead of Count?

No, there is no global setting in Excel to change the default. Excel determines Sum or Count based entirely on the contents of the source column at the moment you build the pivot table. The only reliable way to ensure Sum is always the default is to keep your source data clean: no blanks, no text-formatted numbers, no errors, and no oversized source ranges. Using Power Query to enforce the correct data type before loading is the closest thing to a permanent setting that exists.

Why does my pivot table revert to Count every time I refresh or add new data?

This means the problem is recurring in the source data, not just a one-time issue. Each time you import fresh data from your ERP or another system, the new rows likely contain the same text-formatted numbers or blank cells that triggered Count the first time. Fixing the existing data reactively and then refreshing only solves it temporarily. The permanent fix is to route the import through Power Query with an explicit data type step, so every refresh automatically produces clean numeric data before it reaches the pivot table.

My numbers look fine and are right-aligned, but the pivot table still shows Count. What else could it be?

Right-alignment is a strong sign that Excel sees the values as numbers, but it is not a guarantee. A few less common causes to check: the column may have been grouped (a date or numeric field placed in Rows and then grouped can cause the same field to Count when placed in Values), there may be invisible characters such as non-breaking spaces in some cells (use =LEN() to check if the character count is higher than expected), or the formula bar may show something like ="" rather than a true empty cell in what appears to be a blank. Another case: if your data was imported from a CSV and Excel auto-detected the column type incorrectly during import, the internal type can be text even when the cells align to the right.

Can I change multiple pivot table fields from Count to Sum at once without doing each one individually?

Yes. The fastest manual method is to right-click any value cell in the pivot table, choose Summarize Values By, and select Sum, but this changes only that one field. To change all fields at once, use the VBA macro included in the article above. Paste it into a code module and run it while the pivot table sheet is active. It loops through every field in the Values area and sets each one to Sum in a single pass.

Does replacing blank cells with zero affect my data analysis?

It depends on the calculations you are doing. For Sum totals, replacing blanks with zero has no effect since zero adds nothing. For averages, it does matter: a blank cell is excluded from an average calculation entirely, while a zero is counted as a value and pulls the average down. If your pivot table includes an Average calculation, consider whether those blank rows represent missing data that should be excluded, or whether zero is the correct value. If missing data should be excluded, deleting those rows from the source entirely is a cleaner solution than replacing with zero.

Why does my pivot table show Count for a currency column even though the values look like money?

Currency formatting is cosmetic. It does not change the underlying data type of a cell. If a cell contains the text string “1,250.00” with a currency symbol, it is still text regardless of how it is formatted. This is one of the most common symptoms of ERP and accounting software exports: the export applies currency symbols or thousand separators that Excel cannot parse as numbers. Use =ISNUMBER() to confirm, then use the VALUE function or Text to Columns to convert the column to real numbers. In Power Query, change the column type to Currency or Decimal Number during the import step.

What is the difference between Count and Count Numbers in a pivot table?

Both are available in the Summarize Values By menu, and they behave differently with non-numeric data. Count (which works like COUNTA on a worksheet) counts every non-blank cell including text, errors, and numbers. Count Numbers (which works like COUNT) counts only cells that contain actual numbers and ignores text, errors, and blanks. If you need to count rows in a column that contains a mix of data types, Count Numbers gives you the cleaner result. Neither of these is a substitute for Sum when you need totals of numeric values.