Using Sumifs with Index and Match

[Peter Davison]

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

 

[jasonb75]

From your description

=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]

Brilliant, thankyou for all your help.
It is appreciated.