Results 1 to 15 of 15
  1. #1
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create Totals for Specific Months Automatically (ofiice xp)

    Help, How can I create Running Totals for individual months automatically? So when I alter the date to a new month it will auto calculate.
    I keep a Cash ledger for a club which consists of debit and credit entries which produces a yearly running total automatically, but i need to create a monthly total
    automatically so that when the month changes a new total will be created for the new month etc. Hope this makes sense
    I cuuld create at the end of every month but to do it autmatically would be so much easier!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Create Totals for Specific Months Automatically (ofiice xp)

    Many different ways depending on what you really want to do. If you need to create a new total this would require a macro. If you only need to change an existing formula to reflect the new month this could be done with a formula.

    Could you describe the basics of your setup and what you want to do in a little more detail?

    Steve

  3. #3
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Totals for Specific Months Automatically (ofiice xp)

    Hi Steve,
    Please find attached spreadsheet

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Create Totals for Specific Months Automatically (ofiice xp)

    I'm afraid I don't understand where you want the totals to be and what you want to sum.Also dom of the numbers don't have dates associated with them, are they included?

    Steve

  5. #5
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Totals for Specific Months Automatically (ofiice xp)

    Hi Steve,
    Updated spreadsheet, need to create totals for each month as shown

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Create Totals for Specific Months Automatically (ofiice xp)

    In N3 put in the ARRAY (confirm with ctrl-shift-enter):[Expand the range past row 100 if desired in the formulas]
    =SUM(IF(TEXT($A$3:$A$100,"mmmm")=$M3,($H$3:$I$100) ))

    In O3 enter the Array formula (confirm with ctrl-shift-enter):
    =SUM(IF(TEXT($A$3:$A$100,"mmmm")=$M3,($F$3:$G$100) ))

    In P3 enter the formula (confirm normally with enter)
    =N3-O3

    Copy N3:P3 to N4:P14

    Steve

  7. #7
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Totals for Specific Months Automatically (ofiice xp)

    Steve,
    Thanks for code but I can only get it to update a running total against January and not for Each Individual Month. Am I doing something wrong?( updated Spread sheet attached)
    I need to add up Each Month to show a total, ie After entering all January in i need a total, then when I add in February i need a total for that month and so on throughout the year. I have tried variations on your code but cannot get it to do what i want. Hope you can Help

  8. #8
    Star Lounger
    Join Date
    Feb 2004
    Location
    Jacksonville, Florida
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Totals for Specific Months Automatically (ofiice xp)

    I know this isn't the right formula, but maybe someone with more experience can look at the idea I have going and give you the correct formula: sumif((month($a:$a)&""&year($a:$a)),x15,($g:$g+$h: $h)) Say you're going to have your monthly cum's set up in a column as January 2004, February 2004, etc. You need a formula that says if the month and year of column a matches that of cell x15 (pretend thats where you have January 2004) then add up corresponding columns G&H for that matching date. Then just fill that down the column of dates. Was that what you were trying to accomplish? Then it would have the total per month-or did you want it to cumulate the total as the month goes along?

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Create Totals for Specific Months Automatically (ofiice xp)

    I noticed in your original spreadsheet that you misspelled "Feb<font color=red>r</font color=red>uary". But that is fixed in this one. YOu did not confirm the formula with ctrl-shift-enter.
    Edit the cell <f2> then press ctrl-shift-enter.

    Excel will add "squiggly-brackets" ({}) around it to indicate an ARRAY formula (you should not add them).
    Steve

  10. #10
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Totals for Specific Months Automatically (ofiice xp)

    Thanks Steve,
    All New to Me this stuff good to know I can get Excellent assistance, One last thing can i protect these Formulas if so how?

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Create Totals for Specific Months Automatically (ofiice xp)

    By default, cells are set as "locked" (read "protected"), and must be explicitly "unlocked".
    BUT, the protection is not "in-place" until you protect the worksheet.

    Unprotect the region that you want people to enter in data [Highlight the region, format - cells, Protection (tab) - uncheck "locked"]
    then protect the worksheet [Tools - protection - protect sheet - add a password if desired]

    Another option is to move the calcs to another sheet (you can move the entire region), then use the camera tool to put a "picture-link" of the "calculation region" from the other sheet onto this sheet. No one can change it, since it is only a "picture" of the other cells. It is a link and changes in the sources cells (caused by changes in the data) will keep the picture "live". You can even hide the sheet with the calcs (Format -sheet - hide). This would not require protection of the sheet.

    Steve

  12. #12
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Totals for Specific Months Automatically (ofiice xp)

    Hello Steve,

    Thanks for advice all working now opted for the Camera Tool, Are there lots of little tools the novice doesn't know about? if so where can i find out about them

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Create Totals for Specific Months Automatically (ofiice xp)

    I am sure there are lots of little tips and tricks that novices don't know about: I still find things I didn't know about. The best way is to read books, read the answers to posts here and at other places, ask questions, and the one I, personally, find the best: Experiment with excel.

    Steve

  14. #14
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Totals for Specific Months Automatically (ofiice xp)

    Hi Steve,
    Sorry to bother you again I've been Experimenting, but to no avail Can now sort data by Month and even Category, I have been trying for the past 4hrs to combine both to sort by Month and categary
    From Input, Your help Required. attached test Spread sheet

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Create Totals for Specific Months Automatically (ofiice xp)

    Not sure I understand the problem completely, but here is a as well as a suggestion
    1) to sort "Expenses" sheet by date catefory
    select a date
    data - sort
    sort by: date
    then by: category

    2) Income sheet, soes not need to be sorted "live" since the answers are based on headings. Just sort the headings. Months are already sorted so just sort the category headings:
    Select B1:F1
    Data -Sort...
    [When you get dialog asking if you want to expand, select "continue with current selection"]
    <Sort...>
    <options> press "sort left to right"
    <ok><ok>

    3) If you are looking for the formula to get the correct information (I don't know what this has to do with "sorting") put this ARRAY (confirm with ctrl-shift-enter) into B2:
    =SUM(IF((TEXT(Expenses!$C$14:$C$24,"mmmm")=$A2)*(E xpenses!$G$14:$G$24=B$1),Expenses!$H$14:$H$24))
    Copy this from B2 to B3:B13, then copy B2:B13 to C2:F13

    In this context the "*" is like an AND (a "+" is like an OR). It says to sum the range H14:H24, but only in the rows when the "text of the value C14:C24" equals the value in A2, AND the value in G14:G24 = B1.

    Note I have locked rows and cells in the expense ranges ($C$14:$C$24, $G$14:$G$24, $H$14:$H$24) becaous I want that range locked whenever the formula is copied. I only locked the column in the month comparison ($A2) so the reference "moves" when I copy down the rows, but stays locked on COl A when I copy to the other columns. I only lock the row in the category comparison (B$2) for a similar reason, I always want to compare the value in row 2, but I want to change column comparisons when I change columns.


    My suggestion, the way the data is setup, in expenses and the summary you get in Income, could be done directly with a Pivot table. It would expand the dates and categories as needed and could be sorted directly. This could be done without the need of the array formulas, but would not be "live".

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •