Adding Sums in an Array

[jenniesmith]

I am looking for an array formula that adds the sums in a range rather than just summing the sum cells. This is in case a sum cell needs to be deleted. Any ideas?

 

[Aladin Akyurek]

I am looking for an array formula that adds the sums in a range rather than just summing the sum cells. This is in case a sum cell needs to be deleted. Any ideas?

How do you mean? Care to post a very small sample to show what is needed?

 

[jenniesmith]

Sure. I have several sections within on sheet that have their own sums and I am looking for a way to add the sums together with out just using a =(a1+a5+...). I would like to add the sums in a way that if two of the sections become combined, then the master sum for the sheet would just add each sum rather than look for a reference cell so if I have 3 sums and then I combine two of the sections the master sum equation adjusts to the now two sum cells. Did I make this more confusing?

 

[jenniesmith]

I think I can name the section, but what how would you state the formula to only then add the sums in the range? Thanks to anyone with advice!

 

[just_jon]

? Something like this --

Book1
	A	B	C	D
1	6	66	1	1
2	5	43		2
3	7	99		3
4	4	7	5	4
5	8	5		5
6	4	55		6
7	7	42	8	7
8	5	0		8
9	9	76	9	9
10	23	99		10
11	78	492	23	55
12
13			648
14	One=A1:A10,Two=B1:C10,Three=D1:D10
Sheet1

 

[jenniesmith]

Thanks Jon, but it is more like I have sums D1:d:6, d12:d14, d16:d19... and I would like to recognize every cell in column d that has a cell with in a range becuase I have multiple ranges within Column D. The actual situation is that I have several salespeople and several store types. I want to add the several people within several store types on the same page to get a total of salespeople within the store type, then all the store types together. Periodically, salespeople leave and sometimes two sections are combined which would throw off a sum with specific reference cells. I would like to have the formula look within the range for the occurance of formulas that sum within a range so if the number of cells that sum change up or down the total sum would not have to change.

 

[just_jon]

Is there anything that says when a store-type, by row ? thinking that you might have a cell on each row for such designation. If possible, download and install [new MS MVP!] Colo's utility at the bottom of the page and give us a screenshot for your sheet.

 

[Todd Bardoni]

It almost sounds like you're doing subtotals and want to SUM the result of the subtotals. Is this fair to say?

 

[jenniesmith]

Yes. I can use a subotal system if there is a way to sum below without having to adjust the sum or formula everytime a subtotal is added or deleted above it. Any thoughts?