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().
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
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.