How do I conditionally sum a cell based on the value of another cell?

Question

I have a terrible formula that looks like this:

=if(B17="Yes",B$7)+if(C17="Yes",C$7)+if(D17="Yes",D$7)+if(E17="Yes",E$7)+if(F17="Yes",F$7)

I'd also like to be able to expand it if I go longer than column F.

Basically, other people can change their own row, in this example row 17, but there are many many rows just like it.

user_5632629 · Answer

try:

=INDEX(IFERROR(1/(1/SUM(IF(B17:W17="yes", B$7:W$7, )))))

update

or delete everything in Y17:Y and use this on Y17:

=INDEX(IFERROR(1/(1/MMULT(IF(B17:W="yes", B7:W7, 0), SEQUENCE(COLUMNS(B:W), 1, 1, )))))