Using Sumifs with Index and Match

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
178
Office Version
  1. 365
Platform
  1. Windows
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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,590
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
178
Office Version
  1. 365
Platform
  1. Windows
Brilliant, thankyou for all your help.
It is appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,285
Messages
5,635,314
Members
416,852
Latest member
kanaikls

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