Using a Sum formula with Index and Match

[whizzfizz]

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

 

[Aladin Akyurek]

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

 

[KenWright]

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

 

[chiello]

Does this suit your problem??

Cartel1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Gennaio	Febbraio	Marzo	Aprile	Maggio	Giugno	Luglio	Agosto	Settembre	Ottobre	Novembre	Dicembre
2	2000	1	2	3	4	5	6	7	8	9	10	11	12
3	2001	12	11	10	9	8	7	6	5	4	3	2	1
4	2002
5	2003
6	2004
7	2005
8										Replace		With
9			6							SOMMA		SUM
10										INDIRETTO		INDIRECT
11		Marzo	2000							INDIRIZZO		ADDRESS
12										CONFRONTA		MATCH
13										;		,
14
Foglio1

Post back if you need something different

Ciao

 

[whizzfizz]

Mate, fair dinkum

your a dead set bloody legend!!!!!!!!!!!!!!!!!!

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]

Mate, fair dinkum

your a dead set bloody legend!!!!!!!!!!!!!!!!!!

...
*shouts you a fosters *

You are welcome ... :wink: :wink:

To post sheets on the board Download HtmlMaker 2.42 at

http://www.puremis.net/excel/downloads.shtml

 

[Aladin Akyurek]

Robust and less volatile...

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	2000	1	2	3	4	5	6	7	8	9	10	11	12
3	2001	12	11	10	9	8	7	6	5	4	3	2	1
4	2002
5	2003
6	2004
7	2005
8
9
10
11		Jul	2000	28
12
Sheet1

D11:

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

 

[whizzfizz]

excel-lent!!!

that one worked great. that is so cool

thank you very much again.!!!!