PDA

View Full Version : Solved: How to calculate running totals



Aussiebear
04-06-2010, 08:48 AM
In the attached workbook, the January monthly statment of purchases shows a number of purchases after the initial stock order

I used the following sumproduct to calculate stock levels as of the 1st of the month.
Cell D3=Sumproduct(($B$3:$B$7=B3)*(C3:C7))

However I would now like to calculate the running total at any one date in the subsequent rows. I had thought about using the date field in Column A to determine the last row of the date range to calculate, so is the following concept correct?

Cell D8 =Sumproduct(($A$3:$A$23="<=A8")*($B$3:$B$23=B8)*($D$3:$D$23))


I am looking to count "oranges" between 1/1/10 & 7/1/10 that has been purchased.

Bob Phillips
04-06-2010, 08:56 AM
The first formula doesn't need to be SP, SUMIF is sufficient

=SUMIF($B$3:$B$7,B3,$C$3:$C$7)

and similalrly, SUMIF is good enough for running totals

=SUMIF($B$3:$B$7,B8,$C$3:$C$7)

But won't D8 hold the start of month numbers as well?

Aussiebear
04-06-2010, 03:34 PM
Yes, D8 needs to include all "oranges" purchased since the start of the month until (& including) the the date in A8