Formula to Determine Accumulated Total

Topic

New Reply

Hello,

I’m in need of a formula that would determine the accumulated total for each period. The kicker is that the database will be very large, and will NOT be sorted in an ideal format that would make it easy. I’m trying to avoid a large nested IF formula with a lot of SUMIF’s. I think I’m over-complicating it. If someone can help me think outside the box on this one, it would be greatly appreciated. I’ve attached an Excel file with an example.

Thank you!
Lana

Reply | Quote

Replies

In T2:
=SUMIF(P$1:P2,P2,S$1:S2)

Copy down the column…

Steve

Reply | Quote

Hi Steve,

That was awesome! Wow, I’ve used the SUMIF formula forever… I had know idea it could do that. Why I didn’t ask this question before is beyond me. Anyway, I’m wondering if you could explain the logic behind it for me. I don’t understand how it knows to add OPN+JAN+MAR to get MAR YTD?

Thanks again Steve!!
Lana

Reply | Quote

Hi again!

I just realized that it doesn’t work if there is a period missing from the database. The database is a trial balance, so some will not have all 13 periods, as not all periods will have activity in them. Also, the data may not be sorted in the correct period sequence order (for example, MARCH may be before JANUARY). Can this formula be changed to accomodate these types of variables?

Thanks!!
Lana

Reply | Quote

Lana,

What Steve’s formula does is lock the references to the 1st row (which as text has a value of 0) for both columns P & S, that what the $ does. So when you copy the formula down the column T the other row references will change gradually expanding the Comparison & Sum ranges to add the correct amounts. Missing periods are not a problem, unless you need to report an accumulated value for each period, of course the value of the missing period is the period before it.

As to the sorting problem, as far as I can tell, if you want the correct totals by period they have to be sorted in period order. Why can’t you just sort the worksheet or a copy of it by Acct and then Period Sequence and problem solved. If for some weird reason you can’t sort the data, you could add a column and number the original sequence via a Fill, Sort it, put the formulas in, copy the formula range back on its self as values, resort using the sequence numbers, delete the column w/the sequence numbers…but WHY? :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi Retired Geek,

Thank you for your response. I know it sounds silly that I wouldn’t sort the database. It’s a huge database that I extract from our AS400 by using msquery. I have other columns with formulas next to the msquery that will autofill up and down as the queried database size changes. The sort for these very large database would need to be on the concatenated column, which is NOT formulated inside the msquery, which means the sort would need to be done in Excel. I tried to add the concatenation inside of the msquery so when the data is extracted it would already be sorted the way I want it (and the way you suggested), but it doesn’t work – or I don’t know how to make it work. In a nutshell, I’m trying to avoid having to do any “massaging” of the database “after” it’s been downloaded into Excel. Also, I think if a “sort” action is forgotten, or a macro to “sort” is screwed up and it the “sort” didn’t happen, then the formula is quite vulnerable, and would open the door to producing an inaccurate calculation. I’m trying to maintain control of all the variables, or “what-if” scenarios. I thought there might be a formula that I’m not aware of that would do it. I’m still hopeful there is a formula, or a combination of formula’s that I don’t use regularly that would acheive what I’m trying to accomplish. As usual, any suggestions are much appreciated!

Thanks,
Lana

Reply | Quote

Sorting does not matter to the SUMIF. All you need is to define what column you want to base the subtotal on. I based it on concatenate (as you did in your example). If that column is not correct, what column will be accurate?

Steve

Reply | Quote

Hi Steve,

The concatenated column (column P) is correct. The sumif doesn’t work if the periods are not sorted in chronological order. I’ve attached a new example.

Is there a formula that will do the following:

SUMIF(D:D,D2,H:H) is how I always use the SUMIF formula, but is it possible to modify this formula to do the SUMIF(D:D,D2,H:H), if G:G is <= D2???

Thanks!!
Lana

Reply | Quote

Lana,

You never said what version of Excel you are using. The SUMIFS function looks exactly like what you need. I can’t get it to work on 2003 however even though it says it should! I get a #Name error and it doesn’t show up in the function list even with the Analysis Pack add-in loaded.

Something along this line:

Code:

 =SUMIFS(H:H,D:D, D2,G:G,CONCATENATE( "<",G2))

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Bingo!!! Thanks so much Retired Geek… you rock. This is exactly what I was looking for, and could have used years ago. This will be a new formula that I’ll be sharing with our accounting department on Monday! I’ve been doing a work around to get what I wanted it to do for years (and it involved a lot of columns and formulas). For some reason, I decided yesterday that there had to be an EASIER way… I’ve lost sleep on this one.

Thanks again!
Lana

Reply | Quote

I assume you mean <=G2 not D2…

Since you have an XLS file I assume that this is pre-XL2007. In I2:
=SUMPRODUCT((D$2:D2=D2)*(G$2:G2<=G2)*H$2:H2)

RetiredGeek, SUMIFS was not introduced until XL2007. Before that "standard" way to do multiple conditional ifs was with an array formula…

Steve

Reply | Quote

This is what worked:

=SUMIFS(H:H,D:D,D2,G:G,CONCATENATE(“<=",G2))

I've reattached the example file – I've saved it in Excel 2007.

Thanks again Retired Geek & Steve!! I really appreciate all your help!

Lana

[TABLE="width: 64"]
[TR]
[TD][/TD]
[/TR]
[/TABLE]

Reply | Quote