Forum Discussion
Cell References: How can I use a formula to tell excel what cell to reference?
Cell References: How can I use a formula to tell excel what cell to reference? I am trying to compile a list of results from repeating processes w/in a long sheet. The results I want to referen...
Let's assume you have the numbers 1 through 9 in cells A1:A9.
So just 1, 2, 3, 4 ... 9 underneath each other.
Then you want to add up A3+A6+A9, so every third cell needs to be added.
The result should be 3+6+9 = 18.
You can do that when the row number divided by 3 has a rest/remain value of 0.
The function for that is called Mod()=0
So in A10, this is the formula that works:
=SUM(IF(MOD(ROW(A1:A9);3)=0;A1:A9;0))
Result = 18 🙂
Mind1: this is a spill function, consolidated by the sum() function
Mind2: it's sum(if()), so not sumif()
Mind3: You may want to change ; into , in your area of the world...
So just 1, 2, 3, 4 ... 9 underneath each other.
Then you want to add up A3+A6+A9, so every third cell needs to be added.
The result should be 3+6+9 = 18.
You can do that when the row number divided by 3 has a rest/remain value of 0.
The function for that is called Mod()=0
So in A10, this is the formula that works:
=SUM(IF(MOD(ROW(A1:A9);3)=0;A1:A9;0))
Result = 18 🙂
Mind1: this is a spill function, consolidated by the sum() function
Mind2: it's sum(if()), so not sumif()
Mind3: You may want to change ; into , in your area of the world...