Function Problems

How to Use SUMIF to Sum Values by Day of the Week in Excel

Emma Brown Oct 12, 2026 868 views

Question details

The user needs to sum units based on specific days of the week (e.g., Mon, Tue, Wed) when the source data contains standard date formats.

How to Use SUMIF to Sum Values by Day of the Week in Excel

Product: Excel
Device & OS: not provided
Scenario: Analyzing daily sales, attendance, or other units where aggregating data by weekday is required.
Observed behavior: Source data is stored as standard dates, but the summing criteria require weekday abbreviations, making a direct SUMIF formula fail without prior date conversion.

Before you start

Ensure your source dates are formatted as actual date values in Excel, not as plain text strings, so the formula can accurately extract the weekdays.

Solution 1Recommended

Use a Helper Column with TEXT and SUMIF Functions

The most straightforward method is to extract the weekday abbreviation into a helper column using the TEXT function, then apply the SUMIF function against that new column.

By creating a helper column, you convert standard dates into three-letter weekday text (like 'Mon' or 'Tue'). This provides a direct match for your criteria, allowing SUMIF to accurately calculate the totals.

Add a Helper Column

Insert a new column next to your dates (e.g., Column C). If your dates are in A2:A100, click on C2.

Extract the Weekday Text

Type the formula =TEXT(A2,"ddd") in C2 and press Enter. This converts the date into a three-letter weekday abbreviation (e.g., Mon). Drag the fill handle down to apply this formula to the rest of your dataset.

Set Up Your Criteria

In a separate reference cell (e.g., E2), type the exact weekday abbreviation you want to sum, such as 'Mon'.

Apply the SUMIF Formula

Assuming your units to sum are in B2:B100, select an empty cell for your total and enter =SUMIF($C$2:$C$100,E2,$B$2:$B$100). Press Enter to calculate the total units for that specific day.

Use a Helper Column with TEXT and SUMIF Functions

Generate a Unique Weekday List: If you are using a modern version of Excel (Office 365 or 2021+), you can quickly generate a unique list of weekdays from your dates by typing =UNIQUE(TEXT(A2:A100,"ddd")) into an empty cell.

Solution 2

Calculate Without a Helper Column using SUMPRODUCT

If you prefer not to add extra columns to your dataset, you can combine the SUMPRODUCT and TEXT functions to evaluate and sum values directly in memory.

Efficient Data Analysis

Sum Data by Weekday Effortlessly with WPS Office

WPS Spreadsheet fully supports standard spreadsheet functions like SUMIF, SUMPRODUCT, and TEXT. You can seamlessly perform advanced data aggregation with identical syntax to Microsoft Excel.

1. Open Your Data: Launch WPS Spreadsheet and open your existing Excel workbook (.xlsx) containing the dates and units.
2. Insert the Formulas: Use the formula bar to insert your TEXT and SUMIF functions exactly as you would in standard spreadsheet software.
3. Analyze and Save: Instantly analyze your daily data and save the file with complete format compatibility.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx).Lightweight software with fast processing for large datasets.Supports advanced dynamic array functions including UNIQUE.Free and user-friendly interface that requires no learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Can I sum values using the full weekday name instead of abbreviations?

Yes. To extract the full weekday name (e.g., Monday instead of Mon), change the formatting code in the TEXT function to =TEXT(A2,"dddd"). Ensure your criteria cell also matches the full spelling.

Why is my SUMIF formula returning zero?

This typically happens if the original dates are stored as plain text rather than valid date serial numbers, or if there are leading/trailing spaces in your criteria cell. Verify your date formatting and ensure your criteria perfectly match the extracted text.

Is there a way to sum weekend days only?

You can achieve this by using the WEEKDAY function. For example, =WEEKDAY(A2, 2) returns 6 for Saturday and 7 for Sunday. You can then use SUMIFS to sum values where the calculated weekday number is greater than 5.

Excel & Data Problems