SOLVED

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

Copper Contributor

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 contains 10, 2 = 20, 3 = 30, 4 = 40, 5 = 60 and 6 = 30, would like to add cells 1,2,3 for a total of 60.

6 Replies

@Carlos Monssen 

If you're using 365, this may work for you:

 

=SUM(TAKE(A2:F2,,3))

Patrick2788_0-1675874248400.png

 

 

i am using an older version of excel

@Carlos Monssen 

=SUM(INDEX(A1:F1,1):INDEX(A1:F1,H1))

An alternative could be this formula where the variable is in cell H1.

sum.JPG 

best response confirmed by Carlos Monssen (Copper Contributor)
Solution

@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 

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

You can try this formula.

sum with condition.JPG 

That worked perfectly, I owe you big time, Thanks for all your help
1 best response

Accepted Solutions
best response confirmed by Carlos Monssen (Copper Contributor)
Solution

@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!!

View solution in original post