Sum of values into merged cells

[hasan mougharbel]

Hey guys,
I hope you are all fine.
I have a set of numbers in column A, through which i want to calculate their sums with respect to each merged cell adjacent to the numbers.
My excel file make it so clear.
Thanks for helping me with this.

[tanasedn]

What is the criteria by which you group the values in column A?
Avoid using merged cells as much as possible. If there is a rule according to which the grouping is done, you can use Pivot Table

[FlameRetired]

As tanasedn has already said try to avoid merged cells. They are the devil's spawn. This solution in the attached workbook is a good demonstration of that.

In column D is a helper column. The start of each range is marked with "zzz". Notice the added row in that column with the "zzz". This forces the end of the last range by extending the range 'Given'. In column E is a helper that calculates the row number of each "zzz". If memory serves me you will need to array enter this formula in E4 and fill down.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

It's formula is

=IFERROR(SMALL(IF(N($B$4:$B$30)*($D$4:$D$30="zzz"),ROW($B$4:$B$30)-MIN(ROW($B$4:$B$30))+1),ROWS(E$4:E4)),"")

and I have named it Indices in Name Manager.

In C4 this formula returns the totals. I don't think you will have to array enter it even in Excel 2007. If I am wrong you'll have to fiddle with it a bit. Merged cells hate array formulas.

=IF(D4="zzz",SUM(INDEX(B$4:B$30,SMALL(Indices,COUNTIF(D$4:D4,"zzz"))):INDEX(B$4:B$30,SMALL(Indices,COUNTIF(D$4:D4,"zzz")+1)-1)),"")

[FlameRetired]

Please let us know how this worked out for you. It's been a while since I've used 2007. I didn't check it out on my other machine. So I'm curious if this worked. (I keep "double-o-seven" in another location. LOL)

In the meantime thanks for the added rep and marking your thread Solved.

[wk9128]

First clear the data in this area C4:C27

Then Click the left mouse button to select the ranges area C4:C27
Then Keyboard CTRL+G/Special/Blanks/Key in this formula =SUM(B4:B$29)-SUM(C5:C$29) in Formula Bar then CTRL+ENTER

Please watch this animation teaching file FILE name as SV4.gif
After the file is downloaded and decompressed, you can use the left mouse button to click twice to watch the teaching file (video) or open it with explorer.

[FlameRetired]

@ wk9128, very good! So simple. Thanks for sharing.