Pivot Table Issues

Count Names by Topic in an Excel Pivot Table or Formula

Chanuka GeekiyanageChanuka Geekiyanage Nov 18, 2026 870 views

Question details

Count names listed on separate lines within cells and summarize the counts for each topic.

Count Names by Topic in an Excel Pivot Table or Formula
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet has a Topic column and several columns containing one or more names per cell.
Observed behavior
The desired result is a total number of name entries for each topic, including names in multiline cells.
Before you start

Check that names within each cell are separated by line breaks and identify the Topic column and all columns containing names. These methods count name entries, not distinct people.

Solution 1Recommended

Split and count names by topic with Power Query

Use Power Query to turn multiline cells into individual name rows and group the results by topic.

1
Load the data

Select the data range, choose Data > From Table/Range, and confirm that the table has headers.

2
Unpivot the name columns

In Power Query Editor, select the columns containing names, then choose Transform > Unpivot Columns. Keep the Topic column unchanged.

3
Split names into rows

Select the resulting Value column and choose Split Column > By Delimiter. Enter a line break as the custom delimiter, choose Advanced options > Split into Rows, and confirm.

4
Remove empty entries

With the Value column selected, choose Transform > Format > Trim. Use the column filter to remove blank and null values.

5
Count by topic

Choose Home > Group By, group on Topic, and select Count Rows as the operation. Choose Home > Close & Load to return the totals to Excel.

Split and count names by topic with Power Query
Use a PivotTable instead: To build a PivotTable, load the split name rows before grouping them. Select the loaded table, choose Insert > PivotTable, put Topic in Rows, and put the name column in Values using Count.
Count names in WPS Spreadsheets

Summarize name counts by topic in WPS Spreadsheets

Use a helper-column formula to count line-separated names in each row, then summarize those counts by topic with a PivotTable.

  1. 1. Open the worksheet: Open your workbook in WPS Spreadsheets and check that each cell contains one name per line, with no empty lines.
  2. 2. Add a count column: Add a header such as Name Count beside the data. For names in B2:F2, enter =SUMPRODUCT((B2:F2<>"")*(LEN(B2:F2)-LEN(SUBSTITUTE(B2:F2,CHAR(10),""))+1)) in the first data row.
  3. 3. Fill the formula down: Drag the formula cell's fill handle down through the remaining data rows. Adjust B2:F2 if the name columns are in a different range.
  4. 4. Create the PivotTable: Select the complete data range, including Topic and Name Count, and choose Insert > PivotTable.
  5. 5. Summarize the counts: In the PivotTable field list, place Topic in Rows and Name Count in Values. Set the Values calculation to Sum if needed.
Count names across several cells before grouping totals by topic.Open and save Microsoft Excel XLSX files.Use a familiar spreadsheet interface with formulas and PivotTables.Work with a free, lightweight office suite.
microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't COUNTA count every name in a multiline cell?

COUNTA counts nonempty cells, not the lines within each cell. Split the names into rows in Power Query or use a formula that counts line-separated entries.

How do I count names in a PivotTable after splitting them?

Load the split name rows into Excel, select the resulting table, and choose Insert > PivotTable. Add Topic to Rows and the name column to Values, then set the value calculation to Count.

Will these methods count the same person more than once?

Yes. They count name entries, so a name appearing twice contributes two to the total. For a distinct-person count, standardize name spellings and remove duplicates by topic before counting.

What should I do if the Microsoft 365 formula returns a spill error?

Clear any occupied cells in the output area below and to the right of the formula. Enter the formula outside an Excel table so its results can spill into the worksheet.

Does the WPS helper formula ignore empty lines between names?

No. It counts line breaks plus one in each nonempty cell, so remove empty lines and whitespace-only cells before using it.