How to sum up to a selected month in Excel
Excel formula
=SUM(TAKE(B2:M2,,MATCH($P$1,$B$1:$M$1,0)))Find the month's position and take that many columns: =SUM(TAKE(B2:M2,,MATCH($P$1,$B$1:$M$1,0))), filled down.
- Result on the example
- one result per row in
N2, filled down toN5 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in N2, filled down to N5. Change any input and the result updates, or edit the formula to experiment.
How it works
MATCH finds "Jun" in the header row and returns 6. TAKE(B2:M2,,6) keeps the first six columns of the row, and SUM adds them. Pick a different month in P1 and every row's year-to-date total follows. Google Sheets has no TAKE, so the Sheets version uses ARRAY_CONSTRAIN to keep one row and six columns.
=SUM(TAKE(B2:M2,,MATCH($P$1,$B$1:$M$1,0)))Result78250
B2:M21 row × 12 cols: $12,400, $11,850, $13,200, $12,950, …A range: 12 cells in one row.
$P$1"Jun"The value in P1.
$B$1:$M$11 row × 12 cols: "Jan", "Feb", "Mar", "Apr", …A range: 12 cells in one row.
MATCH($P$1,$B$1:$M$1,0)6MATCH: Position of a value in a list. 0 = exact match.- lookup_value
$P$1"Jun" - lookup_array
$B$1:$M$11 row × 12 cols: "Jan", "Feb", "Mar", "Apr", … - match_type
0
- lookup_value
TAKE(B2:M2,,MATCH($P$1,$B$1:$M$1,0)){1 row × 6 cols} 12400, 11850, 13200, 12950, …TAKE: First (or last, if negative) rows or columns.- array
B2:M21 row × 12 cols: $12,400, $11,850, $13,200, $12,950, … - rows
(omitted) - columns
MATCH($P$1,$B$1:$M$1,0)6
- array
SUM(TAKE(B2:M2,,MATCH($P$1,$B$1:$M$1,0)))78250SUM: Adds numbers and ranges. Skips text and blanks.- number1
TAKE(B2:M2,,MATCH($P$1,$B$1:$M$1,0)){1 row × 6 cols} 12400, 11850, 13200, 12950, …
- number1
In Google Sheets
Google Sheets needs a different formula: =SUM(ARRAY_CONSTRAIN(B2:M2,1,MATCH($P$1,$B$1:$M$1,0))). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((COLUMN($B$1:$M$1)-COLUMN($B$1)<MATCH($P$1,$B$1:$M$1,0))*B2:M2) | $78,250, $134,600, $53,400… | Works in every version: numbers the columns from 0 and keeps those before the selected month's position. |
Common mistakes
- If P1 doesn't exactly match a header (an extra space, "June" vs "Jun"), MATCH returns #N/A. Use a dropdown built from the header row.
- Lock the header range and P1 with
$, or filling down shifts them and later rows return #N/A.
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 up to a selected month
N2, total the department's budget from January through the month in P1, then fill down to N5.Questions
How do I do it in older Excel?
The classic version builds the range with INDEX: =SUM(B2:INDEX(B2:M2,MATCH($P$1,$B$1:$M$1,0))). INDEX returns a cell reference, so B2:INDEX(…) is a range from January to the chosen month.
How do I sum between two selected months?
Use DROP after TAKE: =SUM(DROP(TAKE(B2:M2,,MATCH(end,$B$1:$M$1,0)),,MATCH(start,$B$1:$M$1,0)-1)).
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.