I need the sum of the numbers so that the "SUM function" when adding the rows above doesn't lose its check mark

[Jovinchious]

Hello everyone,

I need the sum of data, for example, from "G57 to G110" which will be expressed in cell "G111", but if I add rows above G57 afterwards, then the formula gets messed up (lose its check mark or give me wrong checks). So, I need a formula that will SUM the data from the first cell above (that is, the last number, for example g110) and the first cell in which the number above appears (for example, g57..while in g56 is string value or blank), but if I add rows above g57, the formula must always include the same data, so that I don't always have to update the formula.

FYI...after that sum, similar tables are repeated...with a few string and blank in-between cells...then again the similar table where I need sums "from g120 to g160" in cell g161...etc.

IDK why my basic SUM formula does not work here...maybe due to old version of XCL...

Thanks in advance!

 

[vladimiratanasiu]

If Office 2007 supports INDIRECT function, maybe next formula helps you. Add above new rows with values / strings and see how it works.

Book1
	A	B
1		1
2
3		aaa
4	1	1
5	2	2
6	3	3
7	4	4
8	5	5
9	6	6
10	7	7
11	8	8
12	9	9
13	10	10
14	11	11
15	12	12
16	13	13
17		7
18	91	99
Sheet1

Cell Formulas
Range	Formula
A18	A18	=SUM(INDIRECT("A1:A"&ROW()-1))
B18	B18	=SUM(INDIRECT("B1:B"&ROW()-1))

 

[Jovinchious]

Thanks a lot!!!

 

[vladimiratanasiu]

If Office 2007 supports INDIRECT function, maybe next formula helps you. Add above new rows with values / strings and see how it works.

Book1
	A	B
1		1
2
3		aaa
4	1	1
5	2	2
6	3	3
7	4	4
8	5	5
9	6	6
10	7	7
11	8	8
12	9	9
13	10	10
14	11	11
15	12	12
16	13	13
17		7
18	91	99
Sheet1

Cell Formulas
Range	Formula
A18	A18	=SUM(INDIRECT("A1:A"&ROW()-1))
B18	B18	=SUM(INDIRECT("B1:B"&ROW()-1))

You're welcome, and you can use "Mark as solution" functionality to indicate the right answer to other members interested for this topic.