How to Count How Many Times a Weekday Appears in an Excel Cell in WPS Spreadsheets
Question details
The user needs a formula to count how many times a weekday name such as Monday appears inside a cell that contains a list of day names, in either older Excel or Excel 365.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a cell containing multiple day names and wants to calculate the occurrence count of a selected weekday name.
- Observed behavior
- The formula should return the correct count of the selected weekday name inside the cell.
Make sure the target day names are consistently separated, and put a valid date containing the weekday you want to count in the reference cell so the TEXT function returns the correct day name.
Count weekday names using LEN and SUBSTITUTE in WPS Spreadsheets or older Excel
Use this compatibility-friendly formula when day names are separated by semicolons and spaces and you need the result to work in older Excel versions and WPS Spreadsheets.
This method removes the target weekday name from the cell text, measures the character difference, and divides it by the length of the weekday name to return the number of occurrences.
In cell B1, enter a valid date whose weekday is the one you want to count, such as 1/1/2024 for Monday.
Click the cell where you want the count to appear, for example B2.
Click the formula bar and enter: =(LEN(A2)-LEN(SUBSTITUTE(A2,TEXT($B$1,"dddd"),"")))/LEN(TEXT($B$1,"dddd"))
Press Enter to calculate the number of times the selected weekday name appears in cell A2.
Count weekday names with TEXTSPLIT and SUM in Excel 365
Use this shorter dynamic array formula when your Excel version supports TEXTSPLIT and you want a direct split-and-compare calculation.
Count Weekday Occurrences in WPS Spreadsheets Without Complex Tools
Use WPS Spreadsheets to count how many times a weekday such as Monday appears in a cell by applying the built-in LEN, SUBSTITUTE, and TEXT functions. It works with your existing .xlsx files and is free to use.
- 1. Download and install WPS Office: Go to the WPS Office download page and install WPS Office on your computer.
- 2. Open your Excel workbook: Launch WPS Spreadsheets and open the .xlsx workbook that contains the day names.
- 3. Enter the reference date: In cell B1, enter a valid date whose weekday you want to count, such as 1/1/2024 for Monday.
- 4. Enter the counting formula: Select the output cell and enter the LEN/SUBSTITUTE formula referencing your day-name cell and B1.
- 5. Press Enter and review the count: Press Enter to calculate the weekday count and confirm the result is correct.
Frequently Asked Questions
Does this formula work if the day names are separated by commas instead of semicolons?
The LEN/SUBSTITUTE method works regardless of the delimiter because it replaces all occurrences of the weekday name in the text. For the TEXTSPLIT method, change the delimiter in the formula from "; " to "," or your actual separator.
Can I count weekdays across multiple cells rather than only one cell?
Yes. Select the output cell and use a SUMPRODUCT version of the LEN/SUBSTITUTE formula over the range, such as =SUMPRODUCT((LEN(A2:A10)-LEN(SUBSTITUTE(A2:A10,TEXT($B$1,"dddd"),"")))/LEN(TEXT($B$1,"dddd"))).
Will the formula count 'Monday' inside 'MondayMorning'?
Yes, LEN/SUBSTITUTE performs substring replacement, so it can count 'Monday' within longer text. If your data can include such words, use a split or exact-match method or ensure day names are separated.
What should I put in cell B1?
Put a valid date whose weekday is the one you want to count, for example 1/1/2024 for a Monday. The TEXT($B$1,"dddd") portion returns the weekday name from that date.
