Search code examples
exceldrop-down-menusumifs

Excel 2010 - Multiple drop downs using data validation and logic

I have a table which contains a list of data I wish to use with dynamic drop down lists. My data looks as follows:

Group   Strength    RX  QTY QTY/RX
Class1  Strength1   1   5.4     5.4
Class2  Strength1   2   10.8    5.4
Class4  Strength1   3   16.2    5.4
Class1  Strength1   3   16.2    5.4
Class1  Strength2   4   42      10.5
Class2  Strength2   2   16.8    8.4
Class4  Strength2   3   25.2    8.4
Class2  Strength4   2   38.4    19.2
Class1  Strength4   8   163.2   20.4
Class2  Strength4   12  182.4   15.2
Class3  Strength4   2   38.4    19.2
Class1  Strength4   8   124.8   15.6
Class1  Strength3   1   24      24
Class2  Strength3   6   72      12
Class1  Strength3   5   60      12

Using the first two columns as drop down lists i.e. drop down list 1 selects from the 'Group' and then drop down list chooses the metric from columns 3-5. In the example below the function will filter on the first column and return the total from column 5 based on the metric strength1 

Group   Class1  <drop down  
Metric  Qty/Rx  <drop down  

Strength1   10.8        
Strength2   10.5        
Strength3   36      
Strength4   36.1

I believe sumifs are the way to go but I can not figure out how to integrate the second drop down to choose columns 3-5, any ideas would be warmly welcomed.

Solution

  • Put the data in cols A through E and the drop-downs in G1 and G2. List the strengths in H3 through H6. Then in I3 enter:

    =SUMPRODUCT(--(A$2:A$16=G$1)*(B$2:B$16=H3)*(OFFSET(A$2:A$16,0,MATCH(G$2,$1:$1,0)-1)))

    and copy downward:

    enter image description here

    This is an example of using SUMPRODUCT() with a variable column based on the column header.