Using a Sum formula with Index and Match

whizzfizz

New Member
Joined
Jan 23, 2005
Messages
5
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. :oops: :oops:

any help would greatly be appreciated.

thank you :biggrin:
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

KenWright

Active Member
Joined
Jan 14, 2005
Messages
267
And please also give the exact references that your defined names 'Profit' and 'Data' represent.
 

chiello

Well-known Member
Joined
Jan 18, 2005
Messages
848
Does this suit your problem??
Cartel1
ABCDEFGHIJKLMN
1GennaioFebbraioMarzoAprileMaggioGiugnoLuglioAgostoSettembreOttobreNovembreDicembre
22000123456789101112
32001121110987654321
42002
52003
62004
72005
8ReplaceWith
96SOMMASUM
10INDIRETTOINDIRECT
11Marzo2000INDIRIZZOADDRESS
12CONFRONTAMATCH
13;,
14
Foglio1


Post back if you need something different

Ciao :coffee:
 

whizzfizz

New Member
Joined
Jan 23, 2005
Messages
5

ADVERTISEMENT

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 (y) *

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 ?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Jan 23, 2005
Messages
5
excel-lent!!!

that one worked great. that is so cool

thank you very much again.!!!!
 

Forum statistics

Threads
1,147,620
Messages
5,742,177
Members
423,708
Latest member
vagosh2001

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top