Microsoft Excel Auto Total Calculation


I am wondering if there is a way to create a sum total in Microsoft Excel for a cumulative range. I have attached a version of the table so that you can see what I mean.

So right now, the total column on the left is set to sum total the row, which is great. But each consecutive row is actually cumulative, not individual. So right now, the first row has a total of $500 and the third row has a total of $800, because the $500 from C4 is the same $500 from C2, not an additional $500.

All I want to do is make the last total (where the 2 totals meet - in this example B7) show the total based on which cell has value. So if there is value in B2, the total in B7 will show that. But if there is a new value in B3, then the total in B7 should then reflect B3 instead of B2. I cannot do a simple sum because this would reflect more (as all of the totals are cumulative).

I know that I can select the B7 total reflect the value by entering "=(cell)", however I am working with a large quantity of figures and worksheets, so I would prefer that Excel do this automatically. Especially if I have to go back and edit one figure in the table at a later date.

Thank you!
KristinaCustomer Retention SpecialistAsked:
Who is Participating?
Naresh PatelConnect With a Mentor TraderCommented:
I will suggest use =MAX(B2:B6) if range is cumulative.

dani gammonProduct Operations ManagerCommented:
Hi Kristina,

I'm sorry if I didn't understand your question correctly.  Are you trying to sum Row B?  so that if you update Row C-Row I, it takes those numbers into account?  

I would use: =sum(b2:b6)

Do you want your total to reflect a sum, a count, or just a cell if it meets certain criteria?  And, if so, what is the criteria?
Rob HensonConnect With a Mentor Finance AnalystCommented:
Along similar lines to ITJockey, but allows for cumualtive total to go down rather than just assuming highest is latest using DMAX function:

In B8 use

In L1:L2 put the following:
L1 =B1  will return "Total"
L2 <>0

This says look at the table and return the value from Total column against the latest date where that value does not equal zero.

Rob H
Rob HensonFinance AnalystCommented:
Sorry, that should be in B7 and the ranges in the formula should stop at 6 rather than 7.

In the version I downloaded I had left your original in B7 and had put mine in B8

KristinaCustomer Retention SpecialistAuthor Commented:
Thank you for the help!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.