COUNTA in Google Sheets Pivot Tables Explained

In a Google Sheets pivot table, COUNTA counts how many non-empty values appear in a selected field for each group. Add the field under Values in the pivot table editor, then choose COUNTA under Summarize by. It counts text and numbers, includes duplicates, and does not mean “count unique records.”

What COUNTA means in a pivot table

COUNTA is the name of a spreadsheet function and a summary option in Google Sheets pivot tables. It is a standard technical label, not an ordinary English word or informal slang. Google documents the function as counting values in a data set, including repeated values and text strings. In everyday terms, it counts populated entries rather than adding their contents.

The name is commonly read as “count A” and is often understood as a contrast with COUNT, which counts numeric values. Google’s function documentation defines what COUNTA does, but does not provide a definitive explanation of how the name was coined. It is safest to treat COUNTA as the established function name rather than claim a more specific origin.

A natural sentence using the term is: “Set the pivot table’s Values summary to COUNTA to count the non-empty order IDs in each region.” The word refers to the aggregation choice, not to a separate kind of pivot table.

How COUNTA works in a pivot

A pivot table groups source rows according to fields you place in Rows or Columns. When you add a field to Values and select COUNTA, Sheets counts the entries in that field within each resulting group. For example, if Region is in Rows and Order ID is in Values, each regional total is the number of non-empty order ID cells for source rows assigned to that region.

COUNTA does not inspect whether the contents are meaningful or unique. A text label, a number, a date, or a repeated ID can each contribute one count when present. A cell that is genuinely empty does not contribute. The Grand total is the count across the records represented in the pivot, not the sum of distinct IDs.

The word “record” matters here: the pivot summarizes source rows. If the same customer appears on five rows, a COUNTA of the Customer field can count five populated entries. It does not automatically reduce those rows to one customer.

How to add COUNTA to a Google Sheets pivot table

These steps are for Google Sheets in a web browser. The source data should have a header row, with one field name per column and no merged cells in the data range. To create or edit a pivot:

Select a cell in the source data, or select the full data range, including its headers.
Choose Insert > Pivot table.
In the dialog, confirm the data range, choose whether to place the pivot table in a new sheet or an existing sheet, and select Create.
In the pivot table editor, add a grouping field under Rows, such as Region.
Under Values, select Add and choose the field to count, such as Order ID.
Open the field’s Summarize by menu and choose COUNTA.

If Sheets initially chooses SUM or another summary, change it in Summarize by. The available summaries can depend on the selected field and the data. To change what is counted, remove or replace the Values field rather than changing the row grouping.

A worked example: count order IDs by region

Suppose a sales list has columns Region, Order ID, and Amount. It contains three North rows: order O-101, a blank Order ID, and order O-103. It also contains two South rows, with IDs O-201 and O-202. Put Region in Rows and Order ID in Values, summarized by COUNTA. The result is North: 2, South: 2, and Grand total: 4. The North row with a blank ID is still part of the source data, but it does not add to the COUNTA result.

For comparison with a regular worksheet function, with the header in B1 and the five example Order ID cells in B2:B6, the formula below returns 4 because four of those five cells contain IDs.

=COUNTA(B2:B6)

The pivot is useful when you want that count broken down by one or more categories, such as region, salesperson, or month. The worksheet function gives a count for the range as a whole; the pivot table automatically repeats the count for each group you configure.

What COUNTA includes—and what it does not

COUNTA is useful when the field being counted is mostly text, such as names, ticket numbers, product codes, or status labels. COUNT is usually a poor substitute for those fields because text entries are not numeric values. Dates are stored as numbers in spreadsheets, but COUNTA can count a populated date cell as well.

Repeated values: counted once per populated source row. Duplicate order IDs are not deduplicated.
Text and numbers: both count when the cell has a value.
Zero and other numeric values: a cell containing 0 is populated and counts.
Whitespace: a cell containing a space is not truly empty and can count. Such cells often look blank on screen.
Empty-looking formula results: Google Sheets’ COUNTA definition includes zero-length text strings. A formula that displays an empty string can therefore be a subtle source of unexpected counts; check those source cells if totals seem too high.
Actually empty cells: these do not contribute to the count.

That last distinction explains many surprising results. A cell can look empty but contain a space, a formula result, or other content. If your goal is to count rows with a valid business identifier, clean or validate the source field first; COUNTA only answers whether it has a value under the function’s rules.

COUNTA compared with other pivot summaries

Choose the summary based on the question you want to answer, not merely on which option Sheets selects automatically. These summaries answer different questions:

COUNTA: How many populated entries are in this field?
COUNT: How many numeric entries are in this field? Text-only IDs may not be counted.
SUM: What is the total of the numeric values? This is appropriate for amounts or units, not for counting text labels.
AVERAGE: What is the arithmetic mean of numeric values? It is not a record count.

COUNTA is also different from a distinct count. If a customer name appears repeatedly, each populated occurrence contributes to COUNTA. For a unique-customer question, use a method that explicitly identifies distinct values, such as a suitable helper column or a separate distinct-count calculation, and check that it handles blanks and spelling variations as intended.

Excel has a COUNTA worksheet function too, but Excel PivotTable value summaries use their own interface and typically label the comparable nonblank aggregation Count. The menu steps and labels above apply specifically to Google Sheets, not Excel.

Common problems and ways to check the result

A pivot total that seems too high or too low is often caused by the source range or the field chosen, rather than a faulty COUNTA calculation. Check the following before changing the summary:

Wrong Values field: Confirm that the pivot counts the field that represents the item you mean to count. Counting a Notes field may produce a different result from counting Order ID.
Blank-looking entries: Inspect cells that appear empty for spaces or formulas returning empty text. These can affect counts.
Duplicates: Remember that each populated duplicate row counts. COUNTA is not a unique-record count.
Incomplete source range: Check the range shown in the pivot table editor. Rows added beyond the selected range may not be included until the range is expanded.
Header or layout problems: Make sure the first row contains clear field names and that there are no blank or merged header cells that make fields hard to identify.
Filters: Review filters in the pivot editor. A filter can remove source records from the displayed summary, so the count may be correct for only the currently included data.

For a practical check, filter or inspect a small group in the source sheet and manually count the populated cells in the same field. If the hand count differs, look for hidden spaces, formula-generated text, duplicate records, or excluded rows. Also check whether the pivot’s source range includes all intended records.

Frequently Asked Questions

Does COUNTA count text in a Google Sheets pivot table?
Yes. COUNTA counts populated text as well as numbers, so it is often useful for fields such as names, codes, or order IDs.

Does COUNTA count duplicate values?
Yes. It counts each populated occurrence in the source rows. If the same ID appears three times in a group, it contributes three, not one.

What is the difference between COUNT and COUNTA?
COUNT counts numeric values, while COUNTA counts values that are not empty, including text. For a text-based ID field, COUNTA is usually the relevant choice.

Why does COUNTA include a cell that looks blank?
The cell may contain a space or a formula that returns empty text. COUNTA can include these values even when they are difficult to see, so inspect the source cell if the total is unexpected.

Can I use COUNTA to count unique customers?
Not by itself. COUNTA counts populated rows, including repeated customer names. Use a distinct-count approach if the question is how many different customers appear, and account for blank entries and inconsistent name spelling.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.