Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to group data by week using formulas instead of a pivot in Excel and Google Sheets
Topic:Pivot-style summaries (formula approach)Excel & Google Sheets
=SUMIFS(B2:B7,A2:A7,">="&A2,A2:A7,"<"&(A2+7))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
- 1Place dates in A2:A7 and amounts in B2:B7.
- 2In a summary table, compute each week's Monday: in D2 enter the first Monday of your range, e.g., =A2-WEEKDAY(A2,3) (returns the Monday of that date's week using mode 3).
- 3In E2 write =SUMIFS(B$2:B$7,A$2:A$7,">="&D2,A$2:A$7,"<"&(D2+7)).
- 4D2+7 is the next Monday, making the range a clean Mon-Sun week without overlap.
- 5Add a label in F2 using =TEXT(D2,"\"Wk\" W - mmm d") for readable week headers.
Tips
- WEEKNUM(date,21) returns ISO week numbers (Monday start) — use it as an additional criterion if you want to match by week number rather than date range.
- To avoid duplicating weeks at year boundaries, always anchor on the week-start date rather than WEEKNUM alone.
- In Google Sheets, ISOWEEKNUM(date) returns the ISO week number directly — handy for SUMIFS criteria alongside YEAR().
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
Why use date-range SUMIFS instead of WEEKNUM as a criterion?
WEEKNUM alone can't be used as a SUMIFS criteria range because SUMIFS needs a cell range, not an array expression. Either add a WEEKNUM helper column or use the date-boundary approach shown here.
How do I handle weeks that span two months?
The date-range approach handles this naturally — a week from Jan 29 to Feb 4 is captured by the >=/< date boundaries regardless of month.
Does this work with fiscal weeks?
Yes. Set your D2 anchor to the first day of your fiscal year's first week, then add 7 for each subsequent row.
More on Pivot-style summaries (formula approach)
See all →Reference
GETPIVOTDATA
Extract a specific value from a PivotTable using field names and item values as explicit identifiers.
How-toHow to build a pivot-style summary with formulas
Build a formula-driven pivot-style cross-tab in Excel or Google Sheets using SUMIFS with mixed references — live totals by row and column without inserting a pivot table.
How-toHow to build a pivot-style summary with QUERY
Use Google Sheets QUERY with GROUP BY, SUM, and LABEL to build a pivot-style summary table without a manual pivot table — Excel alternative uses SUMIFS.
How-toHow to use GETPIVOTDATA to pull values from a pivot table
Learn how GETPIVOTDATA extracts specific values from a pivot table in Excel and Google Sheets, avoiding broken references when the pivot refreshes.
How-toHow to write formulas for a pivot table calculated field
Understand how pivot calculated fields work and when to replace them with SUMIFS-based formulas in Excel and Google Sheets for more control and flexibility.
How-toHow to calculate percent of total without a pivot table
Use SUMIF and SUM to calculate each category's percent of grand total in Excel and Google Sheets — no pivot table required.