SUMIF to Sum By Current Work Week in Google Sheets

Published on

Before we dive into the SUMIF to sum by current work week formula in Google Sheets, it’s important to understand what a work week means in this context.

A work week typically consists of 5 days, spanning from Monday to Friday. However, depending on your region or organization, it can also run from Monday to Saturday or even Sunday to Thursday. We can handle these variations effectively using the WORKDAY function within the SUMIF criteria.

You can use the following formula to sum values for the current work week in Google Sheets:

=ARRAYFORMULA(SUM(SUMIF(A1:A, TODAY()-WEEKDAY(TODAY(), 2)+ROW(A1:A5), B1:B)))

This formula sums the range B1:B corresponding to the dates in column A that fall within the current work week, typically from Monday to Friday.

In the example below, I’ll also explain how to modify this formula to adjust the work week to match your specific needs.

Example of SUMIF to Sum by Current Work Week

Assume you have material purchase dates in column A and quantities in column B. You want to total the quantity received during the current work week, from Monday to Friday.

You can use the formula mentioned earlier in C1.

Example of using the SUMIF function to sum values for the current work week in Google Sheets

The formula dynamically adjusts to the current work week, so you don’t need to update it manually each week.

Adjusting the Work Week in the Formula

To customize the work week, modify the WEEKDAY(TODAY(), 2) part of the formula as follows:

  • WEEKDAY(TODAY(), 2) – For a work week from Monday to Friday.
  • WEEKDAY(TODAY(), 1) – For a work week from Sunday to Thursday.

If you want to include Monday to Saturday (6 working days), use the corresponding WEEKDAY formula and adjust the ROW(A1:A5) part to ROW(A1:A6).

SUMIF to Sum by Current Work Week: Formula Breakdown

Let’s break down the SUMIF to sum by current work week formula:

SUMIF(range, criterion, [sum_range])

Here’s how the formula components are defined:

  • range: A1:A, which is the date column.
  • criterion: TODAY()-WEEKDAY(TODAY(), 2)+ROW(A1:A5), which generates dates for the current work week.
  • sum_range: B1:B, which is the column containing the values to sum.

In the criterion, TODAY()-WEEKDAY(TODAY(), 2) calculates the start of the week (Monday). The ROW function, such as ROW(A1:A5), adds a sequence of days to generate dates for the week.

The SUMIF formula totals column B for rows in column A that match the criterion. This calculates the total values corresponding to the current work week.

The SUM function then aggregates these values into a single total.

We used ARRAYFORMULA for two reasons:

  1. To handle multiple conditions, as SUMIF requires array support.
  2. To create the criterion using an array of dates.

Additional Tip

You can generate dates for the current work week in different ways. In the formula above, we used the following approach:

TODAY()-WEEKDAY(TODAY(), 2)+ROW(A1:A5)

Here, ROW(A1:A5) generates a sequence of numbers. If you prefer, you can replace it with SEQUENCE(5). For a 6-day work week, use ROW(A1:A6) or replace it with SEQUENCE(6).

Alternatively, you can specify the criteria using this formula:

SEQUENCE(5, 1, TODAY()-WEEKDAY(TODAY(), 2))

This SEQUENCE function generates dates in 5 rows and 1 column, starting from TODAY()-WEEKDAY(TODAY(), 2).

If your work week has 6 days, change 5 to 6 in the formula. To start the week from Sunday, replace WEEKDAY(TODAY(), 2) with WEEKDAY(TODAY(), 1).

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Running Total with Structured Table References in Google Sheets

You can use two types of formulas to create a running total with structured...

Automate Multi-Colored Line Charts in Google Sheets

Usually, a line chart with a single line supports only one color. To create...

Calculate the Number of Nights in Each Month in Google Sheets

When working with date ranges, such as check-in and check-out dates, it’s often useful...

Excel Tutorial: Calculate Nights Between Dates in Each Month

When working with date ranges in Excel, you might need to calculate how many...

More like this

Running Total with Structured Table References in Google Sheets

You can use two types of formulas to create a running total with structured...

Automate Multi-Colored Line Charts in Google Sheets

Usually, a line chart with a single line supports only one color. To create...

Calculate the Number of Nights in Each Month in Google Sheets

When working with date ranges, such as check-in and check-out dates, it’s often useful...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.