Weekly and Biweekly Running Totals in Google Sheets

Published on

We typically use SCAN or ARRAYFORMULA + SUMIF + ROW combo to calculate running totals (cumulative sums) in Google Sheets. However, a more complex formula may be required for specific weekly and biweekly running totals in Google Sheets.

I have coded one such array formula for you to use out of the box. It has the following features:

  1. You can control the period using a dropdown menu with two options: Weekly and Biweekly.
  2. You can select the week start day from another dropdown menu with options ranging from Sunday to Saturday.
  3. It is an array formula placed in the range’s topmost (header) row. You do not need to drag down the fill handle of the formula cell or copy-paste it down the column.
  4. You can specify open or closed ranges in the formula depending on whether you have completed data or expect future entries.

The cumulative total by week/biweekly formula requires four input values:

Two equal-sized one-dimensional ranges/arrays point to the date column and the numeric column, for example, A2:A for dates and B2:B for numbers.

Two cell references point to two drop-downs.

The first drop-down should contain the values “Weekly” and “Biweekly,” and the second drop-down should contain the values “Sun,” “Mon,” “Tue,” “Wed,” “Thu,” “Fri,” and “Sat.”

Important: Before using my weekly and biweekly running total array formula, you must sort the two arrays (date and numeric columns) based on the date column in ascending order.

Prerequisites: Drop-down Menus to Select Week Start Day and Running Total Period

We require two data validation drop-down menus. Let’s set them in cells C1 and C2.

First, let’s create the drop-down in cell C1 to control the running total period, i.e., weekly or biweekly.

  1. Select cell C1.
  2. Go to the menu Insert > Drop-down.
  3. In the Option 1 field, type Weekly.
  4. In the Option 2 field, type Biweekly.
  5. Click Done.
Drop-down menu for period selection

Now, let’s create the second drop-down menu in cell C2. This drop-down menu will help the weekly/biweekly running total formula to recalculate at every selected week start day in cell C2.

  1. Select cell C2.
  2. Go to the menu Insert > Drop-down.
  3. In the Option 1 field, type Sun.
  4. In the Option 2 field, type Mon.
  5. Click Add another item and enter Tue in the field that unveils.
  6. Continue the above step and add Wed, Thu, Fri, and Sat.
  7. Click Done.

Arrange the rest of the data below row 3, as per the screenshot below.

Weekly Running Totals in Google Sheets

In the following example, cell range D5:D contains the overtime hours allocated to an employee, and cell range B5:B contains the recorded dates. We don’t require the data in C4:C.

The following formula in cell E4 returns the weekly running totals in E5:E.

Example of weekly running total in Google Sheets

Formula:

=ARRAYFORMULA(LET(r_date,B5:B,r_amt,D5:D,pick,C1,w_d,C2,s_d,SWITCH(w_d,"Sun",1,"Mon",2,"Tue",3,"Wed",4,"Thu",5,"Fri",6,"Sat",7),limit,IF(pick="Weekly",ROUNDUP((DAYS(MAX(r_date),MIN(r_date))+1)/7),ROUNDUP((DAYS(MAX(r_date),MIN(r_date))+1)/14)),dt,SEQUENCE(limit,1,XLOOKUP(s_d,WEEKDAY(r_date),r_date,0,1),IF(pick="WEEKLY",7,14)),f_date,IF(pick="Weekly",dt,dt+7),x_f_date,XLOOKUP(r_date,f_date,f_date,,1,1),VSTACK(pick&" CUSUM",IF(r_date="",,MAP(x_f_date,r_amt,LAMBDA(date,amt,SUMIFS(r_amt,x_f_date,date,ROW(r_amt),"<="&ROW(amt))))))))

The formula selects the dates in B5:B and overtime hours in D5:D and returns the weekly running totals based on the week start day selected in cell C2.

If the selected value in C2 is “Sun,” the formula recalculates the running totals at every “Sunday” row. However, since the selected value in C2 is currently “Mon,” the formula recalculates the running totals at every “Monday” row.

Biweekly Running Totals in Google Sheets

Here, there are no changes in the sample data and the formula. As per my example, you can select “Biweekly” in cell C1 to return the biweekly running totals.

Example of biweekly running total in Google Sheets

This way, we can switch between weekly and biweekly running totals in Google Sheets.

Conclusion

I usually explain each part of the formula for users to learn. However, there have been a few times when I haven’t provided formula explanations due to the complexity of the formula involved.

Anyway, you can use the weekly/biweekly running total array formula out of the box in Google Sheets. If you are interested in learning the formula, please comment and I will share the sample sheet with you.

Here are some CUSUM-related resources:

  1. Custom Named Function for Running Total by Group (Item, Month, or Year) In Google Sheets.
  2. Running Total with Monthly Reset in Google Sheets (Array Formula).
  3. Running Total by Category in Google Sheets (SUMIF Based).
  4. Reset Running Total at Every Year Change in Google Sheets (SUMIF Based).
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.

Lookup Previous Values Dynamically in Excel and Google Sheets

Sometimes, you may want to look up the previous values relative to the current...

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

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.