# How to build a summary table with SUMIFS in Excel

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))`.

**Excel:** `=SUMIFS($D$2:$D$12,$B$2:$B$12,$F2,$A$2:$A$12,">="&G$1,$A$2:$A$12,"<="&EOMONTH(G$1,0))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H | I |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Date | Rep | Region | Amount |  | Rep | 2026-01-01 | 2026-02-01 | 2026-03-01 |
| 2026-01-08 | Ana | West | 1250 |  | Ana |  |  |  |
| 2026-01-15 | Ben | East | 980 |  | Ben |  |  |  |
| 2026-01-29 | Cara | West | 1640 |  | Cara |  |  |  |
| 2026-02-03 | Ana | West | 875 |  |  |  |  |  |
| 2026-02-11 | Ben | East | 1420 |  |  |  |  |  |
| 2026-02-19 | Ana | West | 1105 |  |  |  |  |  |
| 2026-02-27 | Cara | West | 760 |  |  |  |  |  |
| 2026-03-04 | Ben | East | 1310 |  |  |  |  |  |
| 2026-03-12 | Cara | West | 1585 |  |  |  |  |  |
| 2026-03-20 | Ana | West | 940 |  |  |  |  |  |
| 2026-03-31 | Ben | East | 1215 |  |  |  |  |  |

Formula in G2, filled to I4: `=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: $1,250, $1,980, $940; $980, $1,420, $2,525; $1,640, $760, $1,585

## 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.

## Other ways

- `=SUMIFS($D$2:$D$12,$B$2:$B$12,$F2,$A$2:$A$12,">="&G$1,$A$2:$A$12,"<"&EDATE(G$1,1))`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/summary-table-with-sumifs