Apple Event: May 7th at 7 am PT

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Search within date range

I am making a budget based upon the workings of the "personal budget" template.


I am currently looking up Categories in my bank statement and SUM'in the numbers. This works fine!


Because i want to do this by month. i.e. all my food purchases for january 2015 instead of all food purchases in 2015,

i am currently manually selection the cells, but i am thinking that it must be possible for Numbers to check the date range

and only select the numbers based upon a monthly input from me?


My current setup is this:

My statement sheet looks like this, but has 5 years of data from 2010 - present.


Date

Description

CategoryName

Amount

08-11-2010

bla bla bla

Food

-1000,00

08-11-2010

bla bla bla

Clothes

-49,00

08-11-2010

bla bla bla

Movies

-389,90

08-11-2010

bla bla bla

Food

-399,00


My overview sheet looks like this:


Food

Clothes

Movies

2015




January

1000

465346

43

February

4546

346

436

March

42

346

463

April

24546

436

346

May

2456

436

346

June





This way i can keep track of all my spendings.


My current formula is based upon the "Personal budget" formula:

SUMIF(Category;A5;$Amount)


Mine looks like this:

SUMIF(Data::Table 1::$F$217:$F$298;B$3;Data::Table 1::$G$217:$G$298)

Because i am selecting each month manually.


It sums up the chosen category based upon the "A5" which is the category, and then looks up in the category name column and sums the found Amount numbers.
Nice and easy.


Is it not possible to get the formula to do the same but based upon a single month and year?

I could easily type in the month in the formula, that would not bother me, but i do not know what "Select this month" formula to use.....


Hopes this makes sense.

Macbook Pro, Mac OS X (10.4.10)

Posted on Jul 28, 2015 6:33 AM

Reply
Question marked as Best reply

Posted on Jul 28, 2015 6:57 AM

you should be able to use something like this:

User uploaded file

Name the table on the left "Data Entry"


The summary table on the right is setup as shown. Enter the year in cell A2.

B2=SUMIFS(Data Entry::$D, Data Entry::$A, ">="&DATE($A$2, (ROW()−2), 1), Data Entry::$A, "<="&EOMONTH(DATE($A$2, (ROW()−2), 1), 0), Data Entry::$C, B$1)+B3


this is shorthand for, select cell B2, then type (or copy and paste from here) the formula:

=SUMIFS(Data Entry::$D, Data Entry::$A, ">="&DATE($A$2, (ROW()−2), 1), Data Entry::$A, "<="&EOMONTH(DATE($A$2, (ROW()−2), 1), 0), Data Entry::$C, B$1)+B3


select cell B2, copy

select cel B2 to the end of column D (cell D14), paste

5 replies
Question marked as Best reply

Jul 28, 2015 6:57 AM in response to jklarsen

you should be able to use something like this:

User uploaded file

Name the table on the left "Data Entry"


The summary table on the right is setup as shown. Enter the year in cell A2.

B2=SUMIFS(Data Entry::$D, Data Entry::$A, ">="&DATE($A$2, (ROW()−2), 1), Data Entry::$A, "<="&EOMONTH(DATE($A$2, (ROW()−2), 1), 0), Data Entry::$C, B$1)+B3


this is shorthand for, select cell B2, then type (or copy and paste from here) the formula:

=SUMIFS(Data Entry::$D, Data Entry::$A, ">="&DATE($A$2, (ROW()−2), 1), Data Entry::$A, "<="&EOMONTH(DATE($A$2, (ROW()−2), 1), 0), Data Entry::$C, B$1)+B3


select cell B2, copy

select cel B2 to the end of column D (cell D14), paste

Jul 28, 2015 7:04 AM in response to Wayne Contello

hi Wayne,


Thank you for your quick reply, and formula.


Could i ask you if this could be made simpler?

My summary table dates are already setup as dates, there ara all the 1'st of that month. I.e. 01-january-2010.


Could that make the formula simpler? Just in case i change layout at one point ;-)


Sorry for this extra question, i just get easily confused with the formulas ;-(


Very kind regards,


Jan H.

Jul 28, 2015 7:52 AM in response to jklarsen

Jan,


No need to worry about the formula. you can copy and paste. The formula is as simple as I can make it.


Did you try what I posted? Or does it seem to hard? While it is good to understand the formula, you do not need to understand. You simply need to follow the instructions.


There is a typo in what I posted. Here is the correction:

B2=SUMIFS(Data Entry::$D, Data Entry::$A, ">="&DATE($A$2, (ROW()−2), 1), Data Entry::$A, "<="&EOMONTH(DATE($A$2, (ROW()−2), 1), 0), Data Entry::$C, B$1)


select cell B2, copy

select cel B2 to the end of column D (cell D14), paste



What does this formula do?

User uploaded file


If you have a problem post back

Search within date range

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.