Function Problems

How to Count Specific Symbols Across Multiple Ranges in Excel

Emma BrownEmma Brown Nov 10, 2026 870 views

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'.

How to Count Specific Symbols Across Multiple Ranges in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the first result cell

Click on cell AS2, or the specific cell where you want the first range calculation to display.

2
Enter the formula for the first range

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.

3
Enter the formula for the second range

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.

4
Apply formulas to the remaining rows

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.

Array Formula Evaluation: Depending on your version of Excel, you may need to press Ctrl+Shift+Enter to evaluate these array formulas properly. Modern versions, however, process and spill dynamic arrays inherently by just pressing Enter.
Manage Complex Formulas with WPS Spreadsheet

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. 1. Open WPS Spreadsheet: Launch WPS Office on your computer and open the spreadsheet file containing your slot or scheduling data.
  2. 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. 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.
100% compatible with Microsoft Excel formulas, array operations, and file formats (.xlsx).Supports dynamic arrays for complex string calculations and statistical data.Clean, tabbed interface to easily manage multiple large datasets at once.Built-in function templates and formula wizards to speed up daily data analysis.
QA img-9

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.