Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to sum values by week in Excel and Google Sheets
Topic:SUM & aggregationExcel & Google Sheets
=SUMPRODUCT((WEEKNUM(A2:A5,2)=22)*B2:B5)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Put your dates in column A and numeric values in column B.
- 2Decide which week number to sum (e.g. week 22). In an empty cell enter =SUMPRODUCT((WEEKNUM(A2:A5,2)=22)*B2:B5).
- 3Press Enter. The formula multiplies a TRUE/FALSE array (1 for rows in week 22, 0 otherwise) by the values and sums the products.
Tips
- The second argument of WEEKNUM sets the start day: 1 = Sunday, 2 = Monday. Choose consistently across your workbook.
- To make the week number dynamic, replace 22 with a cell reference such as D1.
- In Excel 2010+ and all Google Sheets versions, WEEKNUM is built-in. Older Excel may need the Analysis ToolPak add-in.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What if I want to sum by ISO week?
Use ISOWEEKNUM instead of WEEKNUM: =SUMPRODUCT((ISOWEEKNUM(A2:A5)=22)*B2:B5). Both apps support ISOWEEKNUM.
Can I sum by week across multiple years?
Add a YEAR check: =SUMPRODUCT((YEAR(A2:A5)=2024)*(WEEKNUM(A2:A5,2)=22)*B2:B5).
More on SUM & aggregation
See all →Reference
SUMPRODUCT
Multiply ranges together row by row and sum the result — ideal for weighted totals.
How-toHow to sum a column
Add up an entire column with SUM. Total a whole column or a fixed range — identical in Excel and Google Sheets.
Error fixFix 0
SUM returns 0 or the wrong total in Excel and Google Sheets — fix numbers stored as text, filtered ranges, manual calculation mode, and circular references.
ReferenceSUBTOTAL
Aggregate a range (sum, average, count…) while ignoring other SUBTOTALs and, optionally, hidden rows. Ideal under filters.
How-toHow to create a running total
Build a cumulative running total with a SUM and a clever anchored reference that grows as you copy it down. Works in both apps.
How-toHow to sum values by month
Total amounts that fall in a given month with SUMIFS and two date bounds. Works the same in Excel and Google Sheets.