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

Download the example workbook

*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**:

1 |
=SUMIFS(D3:D9,C3:C9,F3) |

In the above example, we use the TEXT Function to extract the name of the **Day of Week **from the **Sales Date:**

1 |
=TEXT(B3,"dddd") |

The SUMIFS Function then uses this helper column to produce the summary table:

1 |
=SUMIFS(D3:D9,C3:C9,F3) |

## 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:

1 |
=WEEKDAY(B3) |

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:

1 |
=SUMPRODUCT(--(WEEKDAY(B3:B9)=E3),C3:C9) |

Here, the SUMPRODUCT Function performs a complicated “sum if” calculation. Let’s walk through the above example.

This is our final formula:

1 |
=SUMPRODUCT(--(WEEKDAY(B3:B9)=E3),C3:C9) |

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:

1 |
=SUMPRODUCT(--({6; 2; 5; 1; 4; 7; 3}=1),{4; 9; 1; 7; 6; 2; 5}) |

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

1 |
=SUMPRODUCT(--({FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE }),{4; 9; 1; 7; 6; 2; 5}) |

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

1 |
=SUMPRODUCT({0; 0; 0; 1; 0; 0; 0 },{4; 9; 1; 7; 6; 2; 5}) |

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:

1 |
=SUMPRODUCT({0; 0; 0; 7; 0; 0; 0}) |

Finally, the numbers in the array are summed:

1 |
=7 |

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:

1 |
=SUMPRODUCT(--(WEEKDAY(B3:B9)=E3),C3:C9) |

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

1 |
=SUMPRODUCT(--(WEEKDAY($B$3:$B$9)=E3),$C$3:$C$9) |

Read our article on Locking Cell References to learn more.

## Sum If by Day of Week in Google Sheets

These formulas work exactly the same in Google Sheets as in Excel.