Combination of SUMIF,INDEX,MATCH

[ramibassad]

Hello...I am looking for a formula to return total year to date sales by agent if I were to select a month and an agent name

A	B	C	D
Agent / Month	January	February	March
Alex	100	50	40
Michael	200	90	16 ​

Agent: Michael
Month YTD: March

Desired Result: 16+90+200 (the sum of January, February, and March for Michael. If I sekect February instead, I want Excel to only sum up January and February.

Any help?

Thanks!

 

[AlanY]

this works with your sample data

Book1
	A	B	C	D
1		January	February	March
2	Alex	100	50	40
3	Michael	200	90	16
4
5	Agent:	Michael
6	Month YTD:	March
7	Total:	306
262

Cell Formulas
Range	Formula
B7	B7	=SUM(OFFSET($A$1,MATCH(B5,A2:A3,0),1,1,MATCH(B6,B1:D1,0)))

 

[ramibassad]

Thanks. What exactly is the $A$1 reference for? There are no data in A1.

 

[AlanY]

it's a reference cell for the offset() to define the range to sum up

 

[ramibassad]

Okay thanks. This would work if the data is arranged exactly the way I provided in the example about but not otherwise. For example the agent names are not exactly on the same sheet as months and their respective amounts. I think the offset formula needs to be tweaked a bit to reflect that. I will google this offset formula but please do feel free to respond if you have any create ideas I could use to make this work.