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

Question:

Question: Date Range Sums and Category Sums

I have a budget that I am working on, it consists of a Register for the acct, a monthly break down of income and expenses, and finally a monthly "executive summary". I am still learning numbers formulas so I am at a disadvantage here; basically we all know what a check register is, I want to use the dates entered and the income/expenses for those respective dates to separate by month into the "monthly budget" and "summary". I have learned how to make the drop down boxes to change my budget categories and would like to separate those numbers for the monthly detailed report as well. I know how to get the sums and what not added/subtracted per month, thats pretty basic to me. Its the pulling per month that I am not getting. I appreciate any help!


Thanks

MacBook Air, iOS 11.2.6

Posted on

Reply
Question marked as Solved
Answer:
Answer:

Hi ffm,


SUMIFS can be tricky when dealing with dates. If you want to deal directly with comparing dates with 'months', you'll need to have Date and Time values entered in both cells being compared. It can be done, but Numbers is finicky about what is contained in the cells and what is displayed.


An easier method is to employ a 'helper' column to extract the month names from the dates in the data table, and present them as text versions of the month names, This allows you to enter the month names in column A of the summary table, compare those directly with the month names in the helper coumn, a task Numbers takes to more enthusiastically.


Here's an example, using the data in your sample table. The added column is the new column B, which may be hidden.

User uploaded file

Table 2, the summary table, contains two versions of the formula shown below the tables. The version shown is entered in B2, then filled down to the end of column B.

The second version, shown below, is entered in C2 and filled down to the end of column C.


C2: IF(COUNTIF(Table 1::B,A2)<1,"",SUMIF(Table 1::B,A2,Table 1::C))

The part shown in bold is the core formula that does the work—the same formula as used in column B.

The part in 'normal' type is an added 'switch' that prevents calculation until there is at least one copy of the month name for that row in column B of Table 1.


Note: because the left-right order of the Inc and Exp columns is not the same on both tables, this formula cannot be entered in column B, then filled right to work in column C.


Table 1:

Note that I have converted rows 1 and 2 to Header rows, and the bottom two rows to Footer rows. Doing this allows specifying all of the non-header, non-footer cels of a column using only the column letter as the address, as in the Tale 1::B and Table 1::C references above.

This table contains one new formula, entered in B3 and filled down to the last regular cell in column B (in the example, B12).


B3: IF(LEN(A3)<1,"",MONTHNAME(MONTH(A3)))


Like the formula above, this one uses IF as a 'switch' to prevent calculation (and the error that would result) until there is an entry made in 'this row' of column A. If a date has been entered, the bold part of the formula extracts the month value (a number) from the date, then translates that number to the matching month name.


This column should be hidden to prevent accidental damage to the formula.


As you need more row for data entries, you can add then by grabbing the row control handle at the bottom of the row reference tab list and dragging down. The new rows will be added above the footer rows.


Footer row formulas:

These are probably similar to what you already have, but the SUM formulas do have the advantage of being in footer rows.


Table 1::C13: SUM(C)

Table 1::D13: SUM(D)


Table 1::C14: C$1+D13−C13


Regards,

Barry

Posted on

There’s more to the conversation

Read all replies

Feb 25, 2018 5:05 PM in response to ffmwilson In response to ffmwilson

Welcome!


try taking look at the built-in template for the Personal Budget.


To see the template, select the Number menu item "File > New…"


Then click "Personal Finance" on the left, then choose the template. take a look at how this template is done. Ask specific questions here based on what you learned or are stuck on.

Feb 25, 2018 5:05 PM

Reply Helpful

Feb 25, 2018 5:17 PM in response to ffmwilson In response to ffmwilson

The personal budget is the simple budget items which I have down already. Trying to separate the categories out I can probably figure out with trial and error but the data per month from dates in the register isn't covered in that template.

Feb 25, 2018 5:17 PM

Reply Helpful
Question marked as Helpful

Feb 25, 2018 6:15 PM in response to ffmwilson In response to ffmwilson

ffmwilson wrote:


the data per month from dates in the register isn't covered in that template.


Perhaps this expansion of the Personal Budget template (Dropbox download) will do what you want and/or give you ideas that you can use.


SG

Feb 25, 2018 6:15 PM

Reply Helpful (1)

Feb 25, 2018 6:15 PM in response to ffmwilson In response to ffmwilson

Ian-


i messed around with that some but kept getting errors. Here is just a basic setup to what I’m trying to do. I want the numbers I input into the register to combine by month and auto load into the proper month in table 2. Table 2 would be slightly different in set up but this gets the basiC outline to what I’m doing. These would be separate sheets in my final product. Ignore the check mark field. Thanks everyone!


User uploaded file

Feb 25, 2018 6:15 PM

Reply Helpful

Feb 26, 2018 11:39 AM in response to ffmwilson In response to ffmwilson

SG- I downloaded that file you suggested and it helped me with correlating numbers to dates. Now that I know how to do that is one step in the right direction.


Ian- I attempted several times to use the SUMIF (month), not sure what I am doing wrong but I keep getting a syntax error or a not enough/to many values error.

Feb 26, 2018 11:39 AM

Reply Helpful
Question marked as Solved

Feb 26, 2018 4:08 PM in response to ffmwilson In response to ffmwilson

Hi ffm,


SUMIFS can be tricky when dealing with dates. If you want to deal directly with comparing dates with 'months', you'll need to have Date and Time values entered in both cells being compared. It can be done, but Numbers is finicky about what is contained in the cells and what is displayed.


An easier method is to employ a 'helper' column to extract the month names from the dates in the data table, and present them as text versions of the month names, This allows you to enter the month names in column A of the summary table, compare those directly with the month names in the helper coumn, a task Numbers takes to more enthusiastically.


Here's an example, using the data in your sample table. The added column is the new column B, which may be hidden.

User uploaded file

Table 2, the summary table, contains two versions of the formula shown below the tables. The version shown is entered in B2, then filled down to the end of column B.

The second version, shown below, is entered in C2 and filled down to the end of column C.


C2: IF(COUNTIF(Table 1::B,A2)<1,"",SUMIF(Table 1::B,A2,Table 1::C))

The part shown in bold is the core formula that does the work—the same formula as used in column B.

The part in 'normal' type is an added 'switch' that prevents calculation until there is at least one copy of the month name for that row in column B of Table 1.


Note: because the left-right order of the Inc and Exp columns is not the same on both tables, this formula cannot be entered in column B, then filled right to work in column C.


Table 1:

Note that I have converted rows 1 and 2 to Header rows, and the bottom two rows to Footer rows. Doing this allows specifying all of the non-header, non-footer cels of a column using only the column letter as the address, as in the Tale 1::B and Table 1::C references above.

This table contains one new formula, entered in B3 and filled down to the last regular cell in column B (in the example, B12).


B3: IF(LEN(A3)<1,"",MONTHNAME(MONTH(A3)))


Like the formula above, this one uses IF as a 'switch' to prevent calculation (and the error that would result) until there is an entry made in 'this row' of column A. If a date has been entered, the bold part of the formula extracts the month value (a number) from the date, then translates that number to the matching month name.


This column should be hidden to prevent accidental damage to the formula.


As you need more row for data entries, you can add then by grabbing the row control handle at the bottom of the row reference tab list and dragging down. The new rows will be added above the footer rows.


Footer row formulas:

These are probably similar to what you already have, but the SUM formulas do have the advantage of being in footer rows.


Table 1::C13: SUM(C)

Table 1::D13: SUM(D)


Table 1::C14: C$1+D13−C13


Regards,

Barry

Feb 26, 2018 4:08 PM

Reply Helpful

Feb 27, 2018 7:44 PM in response to Barry In response to Barry

Barry-


you are my hero!! Thank you so much! I have a couple more questions about what I’m trying to set up but I have to get this part completely set up first. Hopefully you can answer those questions when I have them!


again THANKS!!!!

Feb 27, 2018 7:44 PM

Reply Helpful

Feb 28, 2018 12:38 PM in response to Barry In response to Barry

Barry I have another question here if you don't mind. In the post where i had my two sheets, in Table 1 is a checkmark in column D. I have it set up (currently) as a pop-up box as a check mark and X for outstanding checks. Is there a way for me to count the X's as Outstanding in table 2 in a new row?

Feb 28, 2018 12:38 PM

Reply Helpful
User profile for user: ffmwilson

Question: Date Range Sums and Category Sums