Results 1 to 12 of 12

20111022, 05:00 #1
 Join Date
 Jul 2002
 Location
 Ankeny, Iowa, USA
 Posts
 344
 Thanks
 8
 Thanked 0 Times in 0 Posts
Formula to Determine Accumulated Total
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 overcomplicating 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

20111022, 06:43 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
In T2:
=SUMIF(P$1:P2,P2,S$1:S2)
Copy down the column...
Steve

20111022, 08:07 #3
 Join Date
 Jul 2002
 Location
 Ankeny, Iowa, USA
 Posts
 344
 Thanks
 8
 Thanked 0 Times in 0 Posts
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

20111022, 08:14 #4
 Join Date
 Jul 2002
 Location
 Ankeny, Iowa, USA
 Posts
 344
 Thanks
 8
 Thanked 0 Times in 0 Posts
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

20111022, 09:06 #5
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,731
 Thanks
 395
 Thanked 1,533 Times in 1,390 Posts
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?May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20111022, 13:18 #6
 Join Date
 Jul 2002
 Location
 Ankeny, Iowa, USA
 Posts
 344
 Thanks
 8
 Thanked 0 Times in 0 Posts
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 "whatif" 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

20111022, 13:24 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20111022, 14:34 #8
 Join Date
 Jul 2002
 Location
 Ankeny, Iowa, USA
 Posts
 344
 Thanks
 8
 Thanked 0 Times in 0 Posts
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,D2,H:H) is how I always use the SUMIF formula, but is it possible to modify this formula to do the SUMIF(D,D2,H:H), if G:G is <= D2???
Thanks!!
Lana

20111022, 15:36 #9
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,731
 Thanks
 395
 Thanked 1,533 Times in 1,390 Posts
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 addin loaded.
Something along this line:Code:=SUMIFS(H:H,D:D, D2,G:G,CONCATENATE( "<",G2))
Last edited by RetiredGeek; 20111023 at 12:48.
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20111022, 16:44 #10
 Join Date
 Jul 2002
 Location
 Ankeny, Iowa, USA
 Posts
 344
 Thanks
 8
 Thanked 0 Times in 0 Posts
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

20111022, 16:49 #11
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
I assume you mean <=G2 not D2...
Since you have an XLS file I assume that this is preXL2007. In I2:
=SUMPRODUCT((D$22=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

20111022, 17:01 #12
 Join Date
 Jul 2002
 Location
 Ankeny, Iowa, USA
 Posts
 344
 Thanks
 8
 Thanked 0 Times in 0 Posts
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