How to sum cells that already contain formula

[terry3218]

Hi I'm making a ledger, and after every few rows there is a cell that contains the sum of preceding few cells with data. Now I need to arrive at the grand total, but summing all the individual cells take a lot of time. Is there a formula that can do this?

[oeldere]

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[terry3218]

Thank you. I have included a sample file. The total of individual accounts are in double bottom and top borders, while I want to total to be displayed in the cells highlighted yellow at the bottom.

[JohnTopley]

Add "Total" in Column D at total calculations rows then


=SUMIF($D$2:$D$75,"Total",E$2:E$75)

and

=SUMIF($D$2:$D$75,"Total",F$2:F$75)



OR (no changes required)

=SUMIFS(E2:E73,$C$2:$C$73,">0",$E$2:$E$73,">0")

and

=SUMIFS(F2:F73,$C$2:$C$73,">0",$F$2:$F$73,">0")

[mehmetcik]

Hi Terry

if you have a lot of numbers in a column and a sum function every few rows

Then adding a new sum at the bottom will give you a grand total.

At least it did for me

Select the red cell in the attached sample

And click on auto sum

Unfortunately empty cells are not allowed in the column.

[terry3218]

Hi Terry

if you have a lot of numbers in a column and a sum function every few rows

Then adding a new sum at the bottom will give you a grand total.

At least it did for me

Select the red cell in the attached sample

And click on auto sum

Unfortunately empty cells are not allowed in the column.

Thanks. It works in the sheet you uploaded, but in worksheet like mine it is only adding the last cell's total rather than all the totals above.

[JohnTopley]

Did you try second solution I offered?

=SUMIFS(E2:E73,$C$2:$C$73,">0",$E$2:$E$73,">0")

and

=SUMIFS(F2:F73,$C$2:$C$73,">0",$F$2:$F$73,">0")

[terry3218]

Add "Total" in Column D at total calculations rows then


=SUMIF($D$2:$D$75,"Total",E$2:E$75)

and

=SUMIF($D$2:$D$75,"Total",F$2:F$75)



OR (no changes required)

=SUMIFS(E2:E73,$C$2:$C$73,">0",$E$2:$E$73,">0")

and

=SUMIFS(F2:F73,$C$2:$C$73,">0",$F$2:$F$73,">0")

Thanks a lot. The one with "no changes required" worked. Can you kindly also briefly explain the logic behind that formula?

[JohnTopley]

Yes, I simply test the column (C) with the date for being >0 i.e.not blank and the amount column E or F >0 which identifies the Total cells.

I now realise the last test is redundant so

=SUMIF($C$2:$C$73,">0",$E$2:$E$73) and =SUMIF($C$2:$C$73,">0",$F$2:$F$73) will work just as well

[JohnTopley]

This also will work ...

=SUMIF($B$2:$B$73,"<>",$E$2:$E$73)

which effectively finds the total fields by checking column B for a blank entry.

[jason.b75]

A simple solution.

With both yellow cells empty. (It works with your test formula in them, but given that they would normally be empty, I thought it best to clarify that it works both ways).

Single click E76

Press Shift & Right Cursor, then hold Shift, Ctrl and Up Cursor together until the selection reaches the top of the sheet. (you will now have E1:F76 selected with E76 as the active cell).*

Press Alt and = together.

Check the results.

edit:-

* You can use any similar method to achieve the selection, the only requirement for it to work is that the active cell is one of those that the formula should be entered into, I just suggested what I feel is the simplest way.
For example, Select F76, Shift, Ctrl and Home, Shift Right x 3, Alt =
Or if you're using a mouse, the traditional method of holding left click and moving up.

[FDibbins]

2 other options...

1. Use SUBTOTAL() instead of any of the SUM functions. You would use ST for the individual sub totals, then 1 again for the crand total, using the whole range - it will ignore all other ST's in teh range

2. you could just some the entire range above, but divide it by 2

[Aardigspook]

As Ford says above, there is a built-in function which does what you want: SUBTOTAL. It can give you sums, averages and lots more, and has the great advantage over the 'normal' Sum/Average/etc that it ignores any other Subtotals in the range you give it. The syntax you want for a sum is =SUBTOTAL(9,range). Assuming you have a lot of data, with a lot of SUMs already there, you could use Find and Replace to replace all the SUMs with SUBTOTALs, like this:
Find: SUM(
Replace: SUBTOTAL(9,

[terry3218]

FDibbins and Aardigspook and all others, Thanks a billion. The subtotal function works like a charm, it saved me hundreds of extra key strokes and energy. Wish I knew about this function earlier. Have a nice day!

[terry3218]

A simple solution.

With both yellow cells empty. (It works with your test formula in them, but given that they would normally be empty, I thought it best to clarify that it works both ways).

Single click E76

Press Shift & Right Cursor, then hold Shift, Ctrl and Up Cursor together until the selection reaches the top of the sheet. (you will now have E1:F76 selected with E76 as the active cell).*

Press Alt and = together.

Check the results.

edit:-

* You can use any similar method to achieve the selection, the only requirement for it to work is that the active cell is one of those that the formula should be entered into, I just suggested what I feel is the simplest way.
For example, Select F76, Shift, Ctrl and Home, Shift Right x 3, Alt =
Or if you're using a mouse, the traditional method of holding left click and moving up.

Wow that worked just as well as the subtotal method, but somehow it only works when I have calculated the individual totals with subtotal function rather than simple sum function. Thanks a lot by the way.

[jason.b75]

Wow that worked just as well as the subtotal method, but somehow it only works when I have calculated the individual totals with subtotal function rather than simple sum function. Thanks a lot by the way.

Was there anything different about the workbook you tried it with when that happened?

For me it worked fine with your sample file from post #3 using sum or subtotal.

Not critical, but I'm curious to find out why it failed for you yet worked for me.

[jason.b75]

FDibbins and Aardigspook and all others, Thanks a billion. The subtotal function works like a charm, it saved me hundreds of extra key strokes and energy. Wish I knew about this function earlier. Have a nice day!

As you're using excel 2013, have a look at the AGGREGATE function as well, it's effectively a new version of SUBTOTAL, just far more versatile.

[terry3218]

Was there anything different about the workbook you tried it with when that happened?

For me it worked fine with your sample file from post #3 using sum or subtotal.

Not critical, but I'm curious to find out why it failed for you yet worked for me.

I am sorry, I am a noob when it comes to such nuances. But, yes, I tried that on a different file than my sample file.

[terry3218]

As you're using excel 2013, have a look at the AGGREGATE function as well, it's effectively a new version of SUBTOTAL, just far more versatile.

Thank you! I'll definitely look into that function.

[Jonmo1]

2. you could just sum the entire range above, but divide it by 2

^ This +1 ^


=SUM(E5:E74)/2

[terry3218]

^ This +1 ^


=SUM(E5:E74)/2

Thank you. I'll try that as well