How to sum across columns between two dates in Excel
Formula (Excel and Google Sheets)
=SUMIFS(B2:G2,$B$1:$G$1,">="&$K$1,$B$1:$G$1,"<="&$K$2)Use SUMIFS sideways, with the date header row as the criteria range: =SUMIFS(B2:G2,$B$1:$G$1,">="&$K$1,$B$1:$G$1,"<="&$K$2).
- Result on the example
- one result per row in
H2, filled down toH4 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in H2, filled down to H4. Change any input and the result updates, or edit the formula to experiment.
How it works
SUMIFS doesn't care whether its ranges run down or across. The header row $B$1:$G$1 holds the week dates, so two date conditions on it pick the weeks from January 12 to February 2, and the sum range B2:G2 is the row to add. The headers and inputs are locked, so filling down only moves the row.
=SUMIFS(B2:G2,$B$1:$G$1,">="&$K$1,$B$1:$G$1,"<="&$K$2)Result535
B2:G21 row × 6 cols: 120, 135, 128, 140, …A range: 6 cells in one row.
$B$1:$G$11 row × 6 cols: 1/5/2026, 1/12/2026, 1/19/2026, 1/26/2026, …A range: 6 cells in one row.
$K$11/12/2026The value in K1.
">="&$K$1">=46034"The & operator joins as text.
$K$22/2/2026The value in K2.
"<="&$K$2"<=46055"The & operator joins as text.
SUMIFS(B2:G2,$B$1:$G$1,">="&$K$1,$B$1:$G$1,"<="&$K$2)535SUMIFS: Adds values that meet every condition.- sum_range
B2:G21 row × 6 cols: 120, 135, 128, 140, … - criteria_range1
$B$1:$G$11 row × 6 cols: 1/5/2026, 1/12/2026, 1/19/2026, 1/26/2026, … - criteria1
">="&$K$1">=46034" - criteria_range2
$B$1:$G$11 row × 6 cols: 1/5/2026, 1/12/2026, 1/19/2026, 1/26/2026, … - criteria2
"<="&$K$2"<=46055"
- sum_range
In Google Sheets
The same formula works in Google Sheets: =SUMIFS(B2:G2,$B$1:$G$1,">="&$K$1,$B$1:$G$1,"<="&$K$2). 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 |
|---|---|---|
| =SUMPRODUCT(($B$1:$G$1>=$K$1)*($B$1:$G$1<=$K$2)*B2:G2) | 535, 32, 128 | The same window as two tests on the header row. |
Common mistakes
- The sum range and the header range must be the same width, and both must be rows. A column-shaped criteria range with a row-shaped sum range gives #VALUE!.
- Headers typed as text ("Jan 5") are not dates. Enter real dates and format them.
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.
Sum across columns between two dates
H2, total the weeks whose header date is from K1 to K2, then fill down to H4.Questions
How do I sum the columns for one month?
Use the first of the month and EOMONTH: ">="&K1 and "<="&EOMONTH(K1,0).
How do I sum columns by header text instead?
Use SUMIF across the header row: =SUMIF($B$1:$G$1,"Actual",B2:G2).
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.