Sum exclude duplicates if below each other

[stimpy123]

I'm trying to work out the follow. I need to write a formula that sums ups the amounts in a column, however these amounts are grouped, the last one in a group should not be taken into account as this is the sum of the group. The other way around could also work, by just summing up the sums of each of the grouped amounts.

A
15.00
5.00
20.00

179.00
179.00

178.00
178.00

Sum Should be 20+179+178 or 15+5+179+178.

Anyone has a best approach for this?

[oeldere]

b2 =

Please Login or Register  to view this content.

and drag down


d2=

Please Login or Register  to view this content.

[stimpy123]

Thanks that works. But would there be a cleaner solution for this?

As I would need to work with new data on this every day. Ideally I'd like to have a formula in one cell at the bottom of the sheet, without needing to manipulate the sheet of data every time around.

[sktneer]

Try this.......

Assuming you have numbers in the range A2:A10 with one blank row below each sum for a group(as shown in the example data), then try this Array Formula which requires confirmation with special key strokes Ctrl+Shift+Enter instead of just Enter. If you enter an array formula correctly, you will notice in the formula bar that the formula gets surrounded by the curly brackets and if you don't see the curly brackets in the formula bar, press F2 (function key), hold down the Ctrl+Shift together and then press Enter to confirm it as an array formula.

Please Login or Register  to view this content.

Does this help?

[stimpy123]

Hi Sktneer,

Thanks for this. I can see what you are doing with this formula, but it doesn't work for some reason, as it returns the { do appear }.

I'm using this:
{=SUM(IF(H2:H62="",H2:H61))}

It is not returning any errors, expect the reference to empty cells.

[sktneer]

In the formula

Please Login or Register  to view this content.

you have to pay attention to the cell references in red.
Where A1= the cell just above your first number and A11=the cell below the last sum of a group, assuming your numbers are in the range A2:A10.

Change it as per your need and if this doesn't work, it would be better if you upload a sample workbook.
See the attached sheet where the formula is applied for the example data in post#1.

[Vikas_Gautam]

Try...

Please Login or Register  to view this content.

Don't forget to click *

[stimpy123]

Thanks got it to work!

[sktneer]

Glad to know that. Thanks for the feedback.
If that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.
Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.

[AlKey]

Maybe this one too

=SUM(A2:A10)/2

Sometimes the very obvious is very difficult to see. Here is an example of it. Each group has the last cell that totals the amount of each group which in fact doubles the amount of all cells. Therefore sum of all cell divided by 2 will get you there result.