Excel: Count Unique Folder Names with a Spilled Formula
Question details
Create a vertical list of distinct folder names from an inventory range and count how many times each folder name appears.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to summarize a large inventory list by folder name using dynamic array formulas or a PivotTable.
- Observed behavior
- The desired result is a spilled list of unique folder names with a corresponding occurrence count for every name.
Make sure the folder-name column has a header and contains no merged cells. Leave enough blank cells below and beside the formula cell so the dynamic array can spill its results.
Use SORT, UNIQUE, and COUNTIF to create a spilled folder summary
Use this method when you want a live, formula-based summary that updates as folder names in the source range change.
Enter the unique-folder formula in one empty column, then use the spill-range reference (#) in the adjacent COUNTIF formula. Replace A2:A1430 with the range that contains your folder names.
Click an empty cell, such as K2, and enter =SORT(UNIQUE(A2:A1430)). Press Enter to spill the unique folder names downward.
Click the cell beside the first result, such as L2, and enter =COUNTIF($A$2:$A$1430,K2#). Press Enter to return a matching spilled list of counts.
Type Folder Name in K1 and Count in L1 to identify the two result columns.
Return folder names and counts with one HSTACK formula
Use this compact option when you want both columns generated from a single formula cell.
Summarize folder names with a PivotTable
Use a PivotTable when you need a refreshable count summary without maintaining formulas.
Create a Dynamic Folder Count Summary in WPS Spreadsheet
WPS Spreadsheet can build a live folder-name summary with dynamic array formulas. Use UNIQUE and SORT to generate distinct folder names, then use COUNTIF with a spill reference to calculate all occurrence counts at once.
- 1. Open the inventory worksheet: Open the Excel inventory file in WPS Spreadsheet and locate the folder-name range, such as A2:A1430.
- 2. Generate distinct folder names: Select an empty cell such as K2, enter =SORT(UNIQUE(A2:A1430)), and press Enter.
- 3. Calculate the counts: Select L2, enter =COUNTIF($A$2:$A$1430,K2#), and press Enter to spill the counts beside the folder names.
- 4. Label and review the results: Enter Folder Name in K1 and Count in L1, then verify that each folder and count appears in the spilled output.
Frequently Asked Questions
Why does my UNIQUE formula show a spill error?
A spill error occurs when one or more cells in the intended output area are not empty, are merged, or are inside an Excel table. Clear the blocked cells, unmerge them, or move the formula to an open range.
How do I exclude blank folder names from the unique list?
Use =SORT(UNIQUE(FILTER(A2:A1430,A2:A1430<>""))) in the unique-list cell. Then use the resulting spill range in COUNTIF, such as =COUNTIF($A$2:$A$1430,K2#).
Can I count unique folder names without sorting them?
Yes. Enter =UNIQUE(A2:A1430) to retain the first-occurrence order from the source list, then enter =COUNTIF(A2:A1430,K2#) in the adjacent column.
Why does COUNTIF use K2# instead of K2?
K2# refers to every value in the dynamic array spilled from K2. This lets COUNTIF return one count for each unique folder name rather than counting only the first folder.
When should I use a PivotTable instead of formulas?
Use a PivotTable when you need a report-style summary that can be refreshed and rearranged. Use UNIQUE and COUNTIF when you want the folder summary to update directly in worksheet cells.
