Skip navigation
This discussion is archived

Need template with daily entries, & monthly sums & year-to-date sums

1179 Views 9 Replies Latest reply: Nov 26, 2012 10:41 PM by Barry RSS
JackieforMacs Calculating status...
Currently Being Moderated
Nov 13, 2012 7:05 PM

I'm trying to find a budget template for Numbers -- adapted from the checkbook template, maybe? --

 

where one can enter daily transactions (with category) in 12 different monthly tables, show each month's category sums in tables next to the daily ones,

 

and also have a table for Year-to-Date category sums, probably at the top of the whole thing.

 

In other words, each individual transaction would have to be added to its monthly category table, AND to the Year-to-Date category table.

 

Has anyone done this?  Can it be done in Numbers?

 

Thanks.

iMac, OS X Mountain Lion (10.8.2)
  • Wayne Contello Level 6 Level 6 (12,570 points)

    You could start with the Existing Number template for a checkbook and duplicate the existing sheet for each month of the year (so there would be twelve).  Then add a 13th sheet to summarize the year.  There would be 12 rows and as many columns as categories.

     

    Just a suggestion.

  • Barry Level 7 Level 7 (29,095 points)

    Hi Jackie,

     

    Why would you want to enter each transaction twice? That introduces a second chance to make an error on one of the entries and leave yourself in a situation where you could spend hours looking through th document trying to find where the error was made.

     

    Enter all of your transactions onto the main table (such as the Transactions table in the Check Register template, then set up the spreadsheet to pull off the transactions for each month onto a separate table without your having to do any retyping.

     

    That would be my suggestion.

     

    Regards,

    Barry

  • Barry Level 7 Level 7 (29,095 points)

    Hi Jackie,

     

    That makes more sense than my original reading. The function you are looking for is "SUMIFS."

     

    Here's an example, with the transactions recorded in the "Data" table, and the January sums reported in the "Summary" table.

    Picture 4.png

    "Data" contains no formulas.

     

    On "Summary":

     

    A1 contains the start date, Jan 1, 2012.

    The rest of column A contains the category names, which must exactly match the names used in the Data table.

     

    B2 contains the formula, which is filled down the rest of column B:

     

    Summary::B2: =SUMIFS(Data::$C,Data::$B,"="&A,Data::A,">="&$A$1,Data::$A,"<="&EOMONTH($A$1,0) )

     

    For an amount to be included in the SUM, three conditions must be met:

    -- The transaction category must match the category in that row of Summary.

    -- The transaction date must be on or after the date in A1 of Summary

    -- The transaction date must be on or before the last day of the month in A1 of Summary.

     

    Regards,

    Barry

  • Barry Level 7 Level 7 (29,095 points)

    "We've also had a frustrating time finding out what templates are in Numbers' package of 100 templates.

    there does not seem to be a preview function on their site.

     

    Do you know how to view them before buying?"

     

    Can't say i'm even aware of the package. What's the site?

     

    Regards,

    Barry

  • Barry Level 7 Level 7 (29,095 points)

    Hi Jackie,

     

    I don't think there is a way to post a file (including a template file) on Apple Support Communities ('this site'). I'd like to see a screen shot and description, though.

     

    You might try the iWork Community site as a place to post the actual template.

     

    Regards,

    Barry

Actions

More Like This

  • Retrieving data ...

Bookmarked By (1)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.