How to build a summary table with SUMIFS in Excel
Formula (Excel and Google Sheets)
=SUMIFS($D$2:$D$12,$B$2:$B$12,$F2,$A$2:$A$12,">="&G$1,$A$2:$A$12,"<="&EOMONTH(G$1,0))Write one SUMIFS with mixed references and fill it across and down: =SUMIFS($D$2:$D$12,$B$2:$B$12,$F2,$A$2:$A$12,">="&G$1,$A$2:$A$12,"<="&EOMONTH(G$1,0)).
- Result on the example
- one result per row in
G2, filled down toI4 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in G2, filled down to I4. Change any input and the result updates, or edit the formula to experiment.
How it works
The references are locked just enough: the data ranges are fully fixed ($D$2:$D$12), $F2 keeps the column so every cell reads the rep from column F, and G$1 keeps the row so every cell reads its month from row 1. One formula then fills the whole grid, each cell picking up its own rep and month.
=SUMIFS($D$2:$D$12,$B$2:$B$12,$F2,$A$2:$A$12,">="&G$1,$A$2:$A$12,"<="&EOMONTH(G$1,0))Result1250
$D$2:$D$1211 rows × 1 col: $1,250, $980, $1,640, $875, …A range: 11 cells in one column.
$B$2:$B$1211 rows × 1 col: "Ana", "Ben", "Cara", "Ana", …A range: 11 cells in one column.
$F2"Ana"The value in F2.
$A$2:$A$1211 rows × 1 col: 1/8/2026, 1/15/2026, 1/29/2026, 2/3/2026, …A range: 11 cells in one column.
G$1Jan 2026The value in G1.
">="&G$1">=46023"The & operator joins as text.
EOMONTH(G$1,0)1/31/2026 (46053)EOMONTH: Last day of the month, months away.- start_date
G$1Jan 2026 - months
0
- start_date
"<="&EOMONTH(G$1,0)"<=46053"The & operator joins as text.
SUMIFS($D$2:$D$12,$B$2:$B$12,$F2,$A$2:$A$12,">="&G$1,$A$2:$A$12,"<="&EOMONTH(G$1,0))1250SUMIFS: Adds values that meet every condition.- sum_range
$D$2:$D$1211 rows × 1 col: $1,250, $980, $1,640, $875, … - criteria_range1
$B$2:$B$1211 rows × 1 col: "Ana", "Ben", "Cara", "Ana", … - criteria1
$F2"Ana" - criteria_range2
$A$2:$A$1211 rows × 1 col: 1/8/2026, 1/15/2026, 1/29/2026, 2/3/2026, … - criteria2
">="&G$1">=46023" - criteria_range3
$A$2:$A$1211 rows × 1 col: 1/8/2026, 1/15/2026, 1/29/2026, 2/3/2026, … - criteria3
"<="&EOMONTH(G$1,0)"<=46053"
- sum_range
In Google Sheets
The same formula works in Google Sheets: =SUMIFS($D$2:$D$12,$B$2:$B$12,$F2,$A$2:$A$12,">="&G$1,$A$2:$A$12,"<="&EOMONTH(G$1,0)). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMIFS($D$2:$D$12,$B$2:$B$12,$F2,$A$2:$A$12,">="&G$1,$A$2:$A$12,"<"&EDATE(G$1,1)) | $1,250 $1,980 $940, $980 $1,420 $2,525, $1,640 $760 $1,585 | Ends the window before the first of the next month, which also includes dates with times on the last day. |
Common mistakes
- Without the
$before F, filling right reads the reps from column G instead. Without the$before 1, filling down reads row 2 as the month. - The month headers must be real dates (the first of each month). Text like "Jan" can't be compared with dates.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Build a summary table with SUMIFS
G2, total the amount for the rep in F2 in the month in G1, then fill the formula across and down to I4.Questions
How do I add a total row and column?
Use SUM on each row and column of the grid, or a SUMIFS with one condition left out.
Can one formula spill the whole table?
In Excel 365, =PIVOTBY(B2:B12,TEXT(A2:A12,"yyyy-mm"),D2:D12,SUM) builds it in one step. In Google Sheets, QUERY with pivot does the same.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.