How to Count Text Entries by Ranking Position in Excel
Question details
The user needs to count how many times a specific text value, such as A, appears in each ranking position when cells contain semicolon-separated ranking lists.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A column contains semicolon-separated ranking values, and the user wants position counts for the value A: how often it is first, second, or in later positions.
- Observed behavior
- The desired result is a row or range showing the count of A in position 1, position 2, and subsequent positions after splitting the semicolon-separated entries.
Confirm that the ranking entries are consistently separated by semicolons and check whether your spreadsheet version supports dynamic array functions such as LET and TEXTSPLIT; if not, use the alternative FILTERXML method or WPS Text to Columns workflow.
Count position values with LET, TEXTSPLIT, TEXTJOIN, and BYCOL in Excel 365
Use this method when you have Excel 365 or another dynamic array-compatible spreadsheet and want a single formula that returns all position counts automatically.
The formula splits each semicolon-separated list into a grid, then applies a column-by-column calculation to count how many cells equal A in each ranking position.
Open the workbook that contains the semicolon-separated ranking list and click the first empty cell where you want the spilled position counts to appear.
In the formula bar, type =LET(input,X1:X3,grid,TEXTSPLIT(TEXTJOIN(",",TRUE,input),";",","),BYCOL(grid,LAMBDA(column,SUM(--(column="A"))))) and change X1:X3 to the range that contains your ranking lists.
Press Enter. Confirm that the first cell shows the count of A in first place, the second cell shows the count of A in second place, and any additional cells show later positions.
Alternative MMULT and FILTERXML formula for Excel versions without dynamic arrays
Use this approach when TEXTSPLIT and BYCOL are not available, such as in Excel 2013 and later for Windows.
Count Ranking Positions in WPS Office Spreadsheets Using Text to Columns and COUNTIF
Open your workbook in WPS Office Spreadsheets, split the semicolon-separated ranking lists into columns, then use COUNTIF to count how often A appears in each position without relying on Excel 365-only functions.
- 1. Download and install WPS Office: Download WPS Office from the official website, install it, and open WPS Spreadsheets.
- 2. Open the Excel workbook with ranking data: Click Menu > Open in WPS Spreadsheets, select the .xlsx or .xls file that contains the semicolon-separated ranking list, and place the cursor in the next empty column.
- 3. Split the ranking list into columns: Select the column with ranking values, go to Data > Text to Columns, choose Delimited, check Semicolon as the delimiter, and click Finish to create one column per ranking position.
- 4. Count A in each position column: In the cells below the split columns, enter =COUNTIF(B2:B4,"A") for the first position, adjust the range for each column, and press Enter to get the counts.
Frequently Asked Questions
How do I count value A in the first and second positions from semicolon-separated cells?
Use =LET(input,X1:X3,grid,TEXTSPLIT(TEXTJOIN(",",TRUE,input),";",","),BYCOL(grid,LAMBDA(column,SUM(--(column="A"))))) in Excel 365, and change X1:X3 to your data range. The spilled results correspond to first position, second position, and later positions.
Why does my formula return #NAME? in Excel?
Functions such as LET, TEXTSPLIT, TEXTJOIN, and BYCOL require Excel 365 or a dynamic array-compatible version. If you see #NAME?, use the FILTERXML and MMULT alternative or split the data with Text to Columns and use COUNTIF.
Can I count a different text value or change the separator?
Yes. Replace "A" with the value you want to count, and change the ";" delimiter in the TEXTSPLIT or SUBSTITUTE function to match your actual separator, such as a comma.
Does WPS Office support these Excel formulas?
WPS Spreadsheets opens Excel workbooks and provides built-in tools such as Text to Columns and COUNTIF to accomplish the same position count. Check your WPS version for LET/TEXTSPLIT support, or use the split-and-count method for a compatible workflow.
