WPS Spreadsheet: Count D, U, and S Characters in Excel Rows
Question details
The user needs to count occurrences of the letters D, U, and S in each spreadsheet row.

- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The data contains text codes across columns such as A2:AO2, and the user wants separate row totals for D, U, and S.
- Observed behavior
- The desired result is a per-row count of every target-letter occurrence, including repeated letters within the same cell.
Confirm the columns containing the text codes, such as A:AO, and check whether the letters should be counted as uppercase only. Leave three empty columns available for the D, U, and S result formulas.
Count every D, U, and S occurrence across each row
Use this formula-based method when target letters may appear multiple times in one cell and you need accurate totals across the entire row.
The LEN and SUBSTITUTE functions compare the original row text with a version that has the target letter removed. SUMPRODUCT adds those character differences across all cells in the row, so repeated letters are counted individually.
In WPS Spreadsheet, click the empty cell where the D total should appear, such as AP2.
Type =SUMPRODUCT(LEN(A2:AO2)-LEN(SUBSTITUTE(A2:AO2,"D",""))) and press Enter. Change A2:AO2 to match your row range if needed.
In the next result columns, enter =SUMPRODUCT(LEN(A2:AO2)-LEN(SUBSTITUTE(A2:AO2,"U",""))) for U and =SUMPRODUCT(LEN(A2:AO2)-LEN(SUBSTITUTE(A2:AO2,"S",""))) for S.
Select the three formula cells, drag the fill handle down, or use Home > Fill > Down to calculate the results for the remaining rows.
Count letters from one source column with separate formulas
Use this shorter approach when all codes are stored in one column rather than spread across several cells in each row.
Count Specific Characters Across Rows in WPS Spreadsheet
WPS Spreadsheet lets you calculate D, U, and S totals with LEN, SUBSTITUTE, and SUMPRODUCT formulas. Enter the formulas in adjacent result columns, then fill them down to process every row while preserving Excel-compatible formatting and formulas.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the row data.
- 2. Choose result columns: Click an empty cell beside the source range, such as AP2, and reserve adjacent cells for the U and S totals.
- 3. Enter the row-count formula: Type =SUMPRODUCT(LEN(A2:AO2)-LEN(SUBSTITUTE(A2:AO2,"D",""))) in the D result cell, replacing A2:AO2 with your actual source range.
- 4. Add the other character formulas: Repeat the formula in the next two columns, replacing "D" with "U" and "S".
- 5. Fill the formulas down: Select the result cells and drag the fill handle down to calculate counts for all required rows.

Frequently Asked Questions
Does the SUMPRODUCT formula count repeated letters in one cell?
Yes. For example, a cell containing DDD contributes three to the D count because LEN compares the original text with the text after every D is removed.
What formula counts D, U, and S across cells A2 through AO2?
Use =SUMPRODUCT(LEN(A2:AO2)-LEN(SUBSTITUTE(A2:AO2,"D",""))) for D. Replace "D" with "U" or "S" for the other totals.
How can I count letters from only one cell per row?
For a source cell such as A2, use =LEN(A2)-LEN(SUBSTITUTE(A2,"D","")); create equivalent formulas with U and S in adjacent result columns.
Why does the formula return zero for lowercase letters?
The formula searches for the exact character entered in SUBSTITUTE. Replace "D", "U", or "S" with the lowercase version when the source data uses lowercase letters.
How do I apply the character-count formulas to all rows?
Enter the formulas in the first data row, select the result cells, and drag the fill handle down or use Home > Fill > Down. WPS Spreadsheet adjusts the row references automatically.




