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.

player0 · Accepted Answer · 2022-03-11T00:11:23.960

0

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, )))))

edited Mar 11 '22 at 00:11

answered Mar 10 '22 at 23:58

player0

@VillAin see the updated answer – player0 Mar 11 '22 at 01:19
The initial answer worked, I solved the circular dependency by moving the cell containing the formula around – Vill Ain Mar 11 '22 at 01:22