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

Question

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

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)

Posted on Nov 13, 2012 7:05 PM

Reply

Answer 1

Nov 13, 2012 8:03 PM in response to JackieforMacs

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.

Reply

Answer 2

Barry

Level 9

59,861 points

Nov 13, 2012 10:56 PM in response to JackieforMacs

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

Reply

Answer 3

Nov 14, 2012 12:55 PM in response to Barry

Barry,

I guess I didn't make myself clear.

The user would enter the transaction amount once...

and then the amount should automatically appear added to that month's Category Table, and

to the Year-to-Date table in the same category, at the same time.

I have not been able to figure out how to design my own template -- it seems beyond me when I get

to the functions --

so I was hoping someone had already figured out this problem,

and created a template.

Thanks to Barry and Wayne for their replies.

Best, Jackie

Reply

Answer 4

Barry

Level 9

59,861 points

Nov 14, 2012 10:42 PM in response to JackieforMacs

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.

"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

Reply

Answer 5

Nov 18, 2012 5:26 PM in response to Barry

Thanks, Barry, I think I get it now.

Now I just need an enthusiastic unpaid intern!

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?

Thanks again,

Jackie

Reply

Answer 6

Barry

Level 9

59,861 points

Nov 19, 2012 10:30 PM in response to JackieforMacs

"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

Reply

Answer 7

Nov 26, 2012 4:21 PM in response to Barry

Barry,

I looked fir it again and I must have misread...

I guess it was the original numbers Templates bundle...

ANYWAY

I finally figured out how to make that template...

I will probably post it

I'm assuming that I would post it on this site....

Any other suggestions?

Thanks, Jackie

Reply

Answer 8

Nov 26, 2012 4:24 PM in response to Wayne Contello

Wayne,

I did wind up using the template basic design you suggested...

Thank you---

I finally figured out the formulas!