Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
running total between two dates
I am trying to have my spreadsheet sum up purchases that fall between (month)
12th to (month) 12th, and when/if a new purchasing month starts, restart summing the purchases. Column G are purchase amts. Column A are purchase dates. Originally, I just referenced month to month, with Column I formula: =MONTH(A5) etc., and my running total forumla: =IF(I4<I5,G5,IF(I5=I4,(G5+J4),0)) but because my purchasing dates are middle of the month, this is not working so well. I would like something I can fill down and not have to rewrite for each date. I've looked through the discussion threads trying to figure out what might work for me but haven't found anything. Thanks in advance for your willingness to help! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
running total between two dates
why don't you try this formula?
SUMIF(range,criteria,sum_range) for example =sumif(I:I,1,G:G) If you can give an example of the data and the wished outcome, it will be helpful to derive the formula. "lolan7" wrote: I am trying to have my spreadsheet sum up purchases that fall between (month) 12th to (month) 12th, and when/if a new purchasing month starts, restart summing the purchases. Column G are purchase amts. Column A are purchase dates. Originally, I just referenced month to month, with Column I formula: =MONTH(A5) etc., and my running total forumla: =IF(I4<I5,G5,IF(I5=I4,(G5+J4),0)) but because my purchasing dates are middle of the month, this is not working so well. I would like something I can fill down and not have to rewrite for each date. I've looked through the discussion threads trying to figure out what might work for me but haven't found anything. Thanks in advance for your willingness to help! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
running total between two dates
Sounds like you want a running total for the month as well as a total running
total. Col J is your running total, right? Add col. J or another for you month totals. If so, try this. Add col. J or another for your month totals. Of course the first entry for your running total column is the data you are adding. In the second row (J), try this. =IF(MONTH(A2)<MONTH(A1),G2,G2+G1) this is presuming your list of transactions is in chronological order starting at the top and the most recent transaction at the bottom. This works because all of your transactions in a particular month have the same month. You might also try taking a look at DATA|SUBTOTALS menu to see if this wouldn't provide better/easier information and displays the information in an easy to read fashion. (using numbers 1-12for the month)As I recall you can elect to put a summary at the bottom .. "lolan7" wrote : I am trying to have my spreadsheet sum up purchases that fall between (month) 12th to (month) 12th, and when/if a new purchasing month starts, restart summing the purchases. Column G are purchase amts. Column A are purchase dates. Originally, I just referenced month to month, with Column I formula: =MONTH(A5) etc., and my running total forumla: =IF(I4<I5,G5,IF(I5=I4,(G5+J4),0)) but because my purchasing dates are middle of the month, this is not working so well. I would like something I can fill down and not have to rewrite for each date. I've looked through the discussion threads trying to figure out what might work for me but haven't found anything. Thanks in advance for your willingness to help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Running Total Grand Total In Pivot Table | Excel Discussion (Misc queries) | |||
running total and average of that total after 3 events | Excel Discussion (Misc queries) | |||
running total and average of that total after 3 events | Excel Discussion (Misc queries) | |||
Formula Needed to Compare Dates and return a running total | Excel Worksheet Functions | |||
RUNNING TOTAL | Excel Worksheet Functions |