# Using a Sum formula with Index and Match

#### whizzfizz

##### New Member
Hi Everyone

im a little stuck with something in excel, and was hoping someone might be able to help me.

I have a set of data with the months across the top (Jan to Dec) for the columns and the years for the rows.

i have created the following formula
=INDEX(Profit,MATCH(\$G\$1,INDEX(Profit,,2),0),MATCH (\$F\$1,INDEX(data,2,),0))

"Profit" is the range name im running the index/match on and "data" is the whole sheet where there is a lot of different information stored.

G1 and F1 are data validation lists that allow the user to pick a month and year and the cell with the index formula will display the profit for that month, for the matching year. This works quite well, however i need to show the profit "year to date".

my question is.. is it possible when the user clicks on say March in the validation list.. i can get the formula to add everything up to and including March (ie Jan feb mar for the selected year) ? Or if they click on December, it adds all 12 months etc...

This has me absolutly stumped how to do it.

any help would greatly be appreciated.

thank you

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

##### MrExcel MVP
Would you post a 5-row by 5-col sample of your data?

#### KenWright

##### Active Member
And please also give the exact references that your defined names 'Profit' and 'Data' represent.

#### chiello

##### Well-known Member
Cartel1
ABCDEFGHIJKLMN
1GennaioFebbraioMarzoAprileMaggioGiugnoLuglioAgostoSettembreOttobreNovembreDicembre
22000123456789101112
32001121110987654321
42002
52003
62004
72005
8ReplaceWith
96SOMMASUM
10INDIRETTOINDIRECT
12CONFRONTAMATCH
13;,
14
Foglio1

Post back if you need something different

Ciao

#### whizzfizz

##### New Member
Mate, fair dinkum

that is exactly what i was looking for...

the 1+2+3 for Jan Feb and March = the 6

thank you so so much!!!

*shouts you a fosters *

i assume i can change the cell references in your example to my range names.

by the way, how did you get the picture into your post ??

cheers
Hooroo
W

EDIT: I re-created your sheet and tried to use your formula so i could work through it, to see how it works, however when i enter the formula excel is telling me there in an error in the formula at \$C\$11. I am using excel 2000 does that make any difference ?

#### chiello

##### MrExcel MVP
Robust and less volatile...
Book5
ABCDEFGHIJKLM
1JanFebMarAprMayJunJulAugSepOctNovDec
22000123456789101112
32001121110987654321
42002
52003
62004
72005
8
9
10
11Jul200028
12
Sheet1

D11:

=SUM(OFFSET(\$B\$2,MATCH(C11,\$A\$2:\$A\$7,1)-1,,1,MATCH(B11,\$B\$1:\$M\$1,0)))

#### whizzfizz

##### New Member
excel-lent!!!

that one worked great. that is so cool

thank you very much again.!!!!

Replies
11
Views
2K
Replies
2
Views
356
Replies
3
Views
929
Replies
1
Views
591
Replies
4
Views
420

1,181,740
Messages
5,931,764
Members
436,802
Latest member
Strycure12

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back