9 Replies Latest reply: Nov 26, 2012 10:41 PM by Barry
Level 1

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)
Solved by Barry on Nov 14, 2012 10:42 PM Solved

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

• Level 6
iWork

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.

• Level 7

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

• Level 1

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 --

and created a template.

Thanks to Barry and Wayne for their replies.

Best, Jackie

• Level 7

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

• Level 1

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

• Level 7

"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

• Level 1

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

• Level 1

Wayne,

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

Thank you---

I finally figured out the formulas!

Best, Jackie

• Level 7

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