# sum if by day of week – Excel & Google Sheets

This tutorial will demonstrate how to use the SUMIFS Function to sum data corresponding to specific days of the week in Excel and Google Sheets. ## Sum If by Day of Week – Text Values

First, we will demonstrate how to sum data corresponding to specific Day of Week names.

We can use the SUMIFS Function, along with a helper column containing the TEXT Function to sum all Sales that take place on a specific Day of Week: In the above example, we use the TEXT Function to extract the name of the Day of Week from the Sales Date: The SUMIFS Function then uses this helper column to produce the summary table: ## Sum If by Day of Week – Using WEEKDAY

Alternatively, weekday numbers can be used. To do this, the helper column in the source data needs to use the WEEKDAY Function: Using the WEEKDAY numerical value instead of the weekday name may provide you with more flexible options for later data visualization tasks and summary functions.

## Sum If by Day of Week – Without Helper Column

The above example summed data by weekday using a helper column. To avoid the need for a helper column, we can use the SUMPRODUCT Function: Here, the SUMPRODUCT Function performs a complicated “sum if” calculation. Let’s walk through the above example.

This is our final formula:

First, the SUMPRODUCT Function evaluates the weekday number of each of the Sales Dates and also lists the array of values from Number of Sales column:

Using the logical test (=1), weekday numbers of 1 are changed to TRUE, all others are FALSE:

Next the double dashes (–) convert the TRUE and FALSE values into 1s and 0s:

The SUMPRODUCT Function then multiplies each pair of entries in the arrays to produce an array of Number of Sales on Sales Dates that match Day of Week 1:

Finally, the numbers in the array are summed:

More details about using Boolean statements and the “–” command in a SUMPRODUCT Function can be found here

## Locking Cell References

To make our formulas easier to read, we’ve shown the formulas without locked cell references:

But these formulas will not work properly when copy and pasted elsewhere in your file. Instead, you should use locked cell references like this: 