# Combination of SUMIF,INDEX,MATCH

##### New Member
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!

### Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
this works with your sample data

Book1
ABCD
1JanuaryFebruaryMarch
2Alex1005040
3Michael2009016
4
5Agent:Michael
6Month YTD:March
7Total:306
262
Cell Formulas
RangeFormula
B7B7=SUM(OFFSET(\$A\$1,MATCH(B5,A2:A3,0),1,1,MATCH(B6,B1:D1,0)))

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

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

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.

Replies
4
Views
197
Replies
1
Views
384
Replies
2
Views
66
Replies
11
Views
818
Replies
3
Views
241

1,203,691
Messages
6,056,756
Members
444,889
Latest member
ibbara

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

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