# How to sum up to a selected month in Excel

Find the month's position and take that many columns: `=SUM(TAKE(B2:M2,,MATCH($P$1,$B$1:$M$1,0)))`, filled down.

**Excel:** `=SUM(TAKE(B2:M2,,MATCH($P$1,$B$1:$M$1,0)))` (Excel 2024 and Microsoft 365)

**Google Sheets:** `=SUM(ARRAY_CONSTRAIN(B2:M2,1,MATCH($P$1,$B$1:$M$1,0)))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Department | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | YTD | Through | Jun |
| Marketing | 12400 | 11850 | 13200 | 12950 | 14100 | 13750 | 12600 | 12100 | 13900 | 14450 | 15200 | 16800 |  |  |  |
| Sales | 21300 | 20850 | 22400 | 23100 | 22750 | 24200 | 23600 | 22900 | 24800 | 25300 | 26100 | 27400 |  |  |  |
| Support | 8600 | 8450 | 8900 | 9100 | 9050 | 9300 | 9200 | 9150 | 9400 | 9600 | 9750 | 9900 |  |  |  |
| R&D | 15800 | 16200 | 15950 | 16700 | 17100 | 16900 | 17400 | 17250 | 17800 | 18100 | 18350 | 18600 |  |  |  |

Formula in N2, filled to N5: `=SUM(TAKE(B2:M2,,MATCH($P$1,$B$1:$M$1,0)))`

Result: $78,250; $134,600; $53,400; $98,650

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

## Other ways

- `=SUMPRODUCT((COLUMN($B$1:$M$1)-COLUMN($B$1)<MATCH($P$1,$B$1:$M$1,0))*B2:M2)`: 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.

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

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/sum-up-to-a-selected-month