Sum weight based on dropdowns

[WaveWalker116]

Hello,

I'm looking for a formula to sum up the weight of a wall based on the variables input through several dropdowns. I found several articles online about adding multiple values based on a single dropdown, but not how to add multiple values based on multiple dropdowns.

On the attached, if you change a dropdown, the formula should go to sheet 2, find the weight, and add it all together.

The first line is a desired result of manually adding the weights up, but not dynamic in that when you change the dropdowns, the weight doesn't update.

Any help is appreciated! Thank you!

EDIT: Sorry, I should have said, I tried the "=SUMIFS(Sheet2!I3:I51,Sheet2!B:B,Sheet1!B2,Sheet2!B:B,Sheet1!C2... etc.) formula and I get a "#VALUE" error... I'm guessing because each criteria range is the same range?

[WaveWalker116]

So, I found I can do it with:

=SUMIF(Sheet2!B:B,B2,Sheet2!I:I)+SUMIF(Sheet2!B:B,C2,Sheet2!I:I)+SUMIF(Sheet2!B:B,D2,Sheet2!I:I)+SUMIF(Sheet2!B:B,E2,Sheet2!I:I)+SUMIF(Sheet2!B:B,F2,Sheet2!I:I)+SUMIF(Sheet2!B:B,G2,Sheet2!I:I)+SUMIF(Sheet2!B:B,H2,Sheet2!I:I)

Is there any nicer way of doing it?

[Glenn Kennedy]

mAYBE:

=LET(A,Sheet2!$A$2:$A$60,B,Sheet2!$B$2:$B$60,C,Sheet2!$I$2:$I$60,D,$B$1:$H$1,E,B2:H2,

F,SCAN("",A,LAMBDA(x,y,IF(y="",x,y))),

IFERROR(1/(1/SUM(MAP(D,E,LAMBDA(g,h,FILTER(C,(F=g)*(B=h)))))),""))

[windknife]

Another way

J4
=SUMPRODUCT((B4:H4=Sheet2!$B$2:$B$52)*Sheet2!$I$2:$I$52)

[WaveWalker116]

Those work! Thank you!!