How to Count Specific Symbols Across Multiple Ranges in Excel
Question details
The user needs to count the occurrences of the symbols 'U' and 'S' across multiple specific cell ranges, assigning a full value of 1 for 'U' and a half value (0.5) for 'S'.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating total slot allocations or scheduling marks across two different sets of ranges (e.g., C2:V2 and X2:AQ2) using weighted math in a single formula.
- Observed behavior
- A cohesive formula is needed to identify specific text strings, calculate their frequency, and apply weighted math to determine a final accurate slot count.
Ensure that your target cells contain the exact specific symbols ('U' and 'S') as intended without hidden trailing spaces, and verify that the cell ranges you plan to calculate are continuous.
Use SUM, LEN, and SUBSTITUTE Formulas to Count and Weigh Symbols
This method calculates the total occurrences of 'U' and 'S' within a range by measuring the string length differences before and after removing the specific symbols, then applying the respective weight.
The LEN function counts the total characters in a given range. By nesting the SUBSTITUTE function inside LEN, we can replace the target symbol with a blank and see how much the total character count shrinks. This shrinkage equals the number of times that specific letter appears. Finally, dividing the count of 'S' by 2 perfectly applies the half-slot requirement.
Click on cell AS2, or the specific cell where you want the first range calculation to display.
Input the formula =SUM(LEN(C2:V2)-LEN(SUBSTITUTE(C2:V2,"U","")))+SUM(LEN(C2:V2)-LEN(SUBSTITUTE(C2:V2,"S","")))/2 into the formula bar and press Enter.
Click on cell AT2 (or your next output cell) and input the formula =SUM(LEN(X2:AQ2)-LEN(SUBSTITUTE(X2:AQ2,"U","")))+SUM(LEN(X2:AQ2)-LEN(SUBSTITUTE(X2:AQ2,"S","")))/2, then press Enter.
Highlight cells AS2 and AT2, then click and drag the fill handle (the small square at the bottom right corner of the selection) down to automatically apply these array formulas to the rest of your data rows.
Count Characters and Calculate Ranges Effortlessly in WPS Office
WPS Spreadsheet provides robust support for advanced array formulas, including seamless processing of SUM, LEN, and SUBSTITUTE functions. It flawlessly processes weighted calculations across multiple large ranges.
- 1. Open WPS Spreadsheet: Launch WPS Office on your computer and open the spreadsheet file containing your slot or scheduling data.
- 2. Input the Array Formula: Select your target calculation cell and paste the provided SUM, LEN, and SUBSTITUTE formula into the top formula bar.
- 3. Execute the Calculation: Press Enter to instantly calculate the exact weighted occurrences of your U and S symbols, then drag the fill handle to apply it downward.

Frequently Asked Questions
Can I make this formula case-insensitive so it counts lowercase 'u' and 's'?
Yes. By default, the SUBSTITUTE function is case-sensitive. To make it case-insensitive, you need to wrap the range inside the UPPER function, changing the substitute segment to SUBSTITUTE(UPPER(C2:V2), "U", "").
Why am I getting a #VALUE! error when using this formula?
This typically happens in older spreadsheet versions if the array formula is not confirmed properly. Click into the formula bar and press Ctrl+Shift+Enter instead of just Enter to fix it.
How can I modify the formula to add another symbol, like 'H' for quarter slots?
You can append another SUM and SUBSTITUTE segment to the end of the existing formula: +SUM(LEN(C2:V2)-LEN(SUBSTITUTE(C2:V2,"H","")))/4. This will count the occurrences of 'H' and divide them by 4.




