# Using Sumifs with Index and Match

#### Peter Davison

##### Board Regular
Hi
I'm trying to add a column of sales (which is constant), but the criteria column I need to add is based on a user choice of criteria.
I'm sure it is a combination of Sumifs, Index and Match, but I can't seem to crack it.

My sum range is in Sheet - Master Data column U
My Criteria range can be one of a range of columns on the Master Data sheet BA (Heading L1), BB (Heading L2), BC (Heading L3) and BD (Heading L4)
I have a cell in Sheet('Dashboard2'!B66) the user chooses and enters one of the headings from above (L1, L2, L3, L4)

So I am trying to do Sumifs with the criteria range from the user choice
My formula will sit in the Master Data Sheet in cell BE3 and will look at one of the 4 options (L1, L2, L3 L4 columns which sit in the Master Data Sheet columns BA, BB, BC, BD)

Any help would be appreciated.
Thanks

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### jasonb75

##### Well-known Member
From your description
Excel Formula:
``=SUMIFS(\$U:\$U,INDEX(\$BA:\$BD,0,MATCH('Dashboard2'!\$B\$66,\$BA\$1:\$BD\$1,0)),"Criteria")``
You gave us the ranges but not the criteria, so I've just used the word "Criteria" as a placeholder.

#### Peter Davison

##### Board Regular
Brilliant, thankyou for all your help.
It is appreciated.

Replies
8
Views
132
Replies
0
Views
73
Replies
3
Views
21
Replies
8
Views
89
Replies
3
Views
530

Threads
1,129,528
Messages
5,636,837
Members
416,945
Latest member
Himu

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

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