summing a variable number of cells based on a number in another cell

I have a row of 6 cells and I want to add 1 cell when the number in another cell outside the range is 1, 2 cells when it is 2, 3 cells when it is 3. assume that the variable is 3, cells 1 contain...

Formulas and Functions

Carlos Monssen
Feb 08, 2023
OliverScheurich

=SUM(INDEX(J2:Q2,1):INDEX(J2:Q2,R2))/R2

That got me the correct results and I added the divide worked great, now I want to add 1 additional condition

=If I2>2,SUM(INDEX(J2:Q2,1):INDEX(J2:Q2,R2))/R2,c2

In the above I want to make the sum conditional if i2 is greater than 2 and if not I want to use the value in c2. I am not an expert and your help is greatly appreciated. this is for our golf league and calculating our league handicaps!!

Carlos Monssen

Copper Contributor

Feb 08, 2023

OliverScheurich

=SUM(INDEX(J2:Q2,1):INDEX(J2:Q2,R2))/R2

That got me the correct results and I added the divide worked great, now I want to add 1 additional condition

=If I2>2,SUM(INDEX(J2:Q2,1):INDEX(J2:Q2,R2))/R2,c2

In the above I want to make the sum conditional if i2 is greater than 2 and if not I want to use the value in c2. I am not an expert and your help is greatly appreciated. this is for our golf league and calculating our league handicaps!!

OliverScheurich

Gold Contributor

Feb 08, 2023

Carlos Monssen

=IF(I2>2,SUM(INDEX(J2:Q2,1):INDEX(J2:Q2,R2))/R2,C2)

You can try this formula.

Carlos Monssen
Copper Contributor
Feb 09, 2023
That worked perfectly, I owe you big time, Thanks for all your help

Forum Discussion

summing a variable number of cells based on a number in another cell

Resources