Count Names by Topic in an Excel Pivot Table or Formula
Question details
Count names listed on separate lines within cells and summarize the counts for each topic.

- 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.
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.
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.
Select the data range, choose Data > From Table/Range, and confirm that the table has headers.
In Power Query Editor, select the columns containing names, then choose Transform > Unpivot Columns. Keep the Topic column unchanged.
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.
With the Value column selected, choose Transform > Format > Trim. Use the column filter to remove blank and null values.
Choose Home > Group By, group on Topic, and select Count Rows as the operation. Choose Home > Close & Load to return the totals to Excel.

Count names by topic with a Microsoft 365 formula
Use a dynamic array formula when your Excel version supports LET, MAP, TEXTSPLIT, BYROW, and HSTACK.
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. Open the worksheet: Open your workbook in WPS Spreadsheets and check that each cell contains one name per line, with no empty lines.
- 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. 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. Create the PivotTable: Select the complete data range, including Topic and Name Count, and choose Insert > PivotTable.
- 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.

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.




