SUMIF (OR SUMIFS) Using data from named ranges in dynamic drop down list

Hello all,

I have a formula that reads as follows:

=SUMPRODUCT(SUMIF(Table1[Item No.],B6,Table1[Order Total])). The formula works fine as-is, but I've recently created a dynamic drop down list in my sheet, and I want to know if I can have the formula reference some of the named ranges that are chosen in the drop down list.

for example, the formula above will calculate the order total of any given Item Number that's entered into cell B6 (all of the info is listed in an Excel-defined table named 'Table1'). My dynamic drop-down list spans two cells. The first cell (B5) contains all of the headers in Table1 (Item No., Type, Sub-Type, Destination Code). The second cell (B6), will list all of the data that is entered in Table1 that falls under the header chosen in cell B5. I'd like to be able to choose from the drop down list and have the formula reference the named range chosen (i.e. =SUMPRODUCT(SUMIF(Table1[Type],B6,Table1[Order Total])) or =SUMPRODUCT(SUMIF(Table1[Destination Code],B6,Table1(Order Total])).

The only piece of data that is changing is the range in my formula. The criteria is always going to be listed in cell B6 even though it's chosen from the drop-down, and the sum range is always going to be "Order Total" in Table1.

is there any way to accomplish this?

Hi Pepe,

Thanks. I built a new, simplified sheet to try and illustrate what I'm looking to accomplish.

The cells highlighted in yellow are what I currently have. I can run the formula to add the quantity and extension of any particular order, if I enter the 'order number' into the appropriate field ('1880' is entered in the example).

What i want to do, is get use the drop-down list i've created (highlighted in red), where i can choose the headings and values and have the formula automatically update. I hope i've explained this correctly!