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.

Count Consecutive Duplicates in Excel (Dynamic Array Formula)

Counting consecutive duplicates in Excel is useful for analyzing patterns, detecting repetitive sequences, and...

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Filter Top N per Category in Excel (Using FILTER & COUNTIFS)

You can use a combination of FILTER and COUNTIFS to filter the top N...

More like this

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Google Sheets: Extract Top N per Group from Query Aggregation

When working with grouped and aggregated data in Google Sheets, you might need to...

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.