Fully dynamic SUMIF array formula

Question

Goal: Make a fully dynamic SUMIF array formula in Excel. Sample sheet:

Cells C10, D10, and C11 will change, and the formula will need to still work. For example, if C10 became ID and C11 became 171, The formula should sum all weights where the ID column row equaled 171.

Answer 1

You mentioned that D10 will change as well as C10 and C11 but your sample data gives no indication as to what column of information to make a sum from. I've adjusted your sample data to suit.

        

1. Start with C10:D11 blank.
2. Select C10 and choose Data ► Data Tools ► Data Validation. Select Allow: List and supply the following for the Source: =$B$1:$C$1.
3. Select D10 and go back to Data Validation. Select Allow: List and supply the following for the Source: =$D$1:$E$1.
4. Select C11 and go back to Data Validation. Select Allow: List and supply the following for the Source: =INDEX($B$2:$C$7, 0, MATCH($C$10, $B$1:$C$1, 0)).
5. Put the following formula into D11: 
         =SUMIFS(INDEX($D$2:$E$7, 0, MATCH($D$10, $D1:$E1, 0)), INDEX($B$2:$C$7, 0, MATCH($C$10, $B$1:$C$1, 0)), $C$11) 

You should now have three drop-downs in C10:C11, D10 and a dynamic SUMIFS function total based on the criteria you select.