I would like to know what the function is that would take a look at a date range, and extract the month name
Here is how I would like it to come out:
MacBook Pro with Retina display, OS X Mavericks (10.9.3), 15-inch, Late 2013
Basically I need to limit totals from the whole year to each month.
QUESTION:
I want the "Monthly Total" tables to reference the expense table's dates so that the totals based on "Category" and "Amount"
can be taken for each month individually without having to break up the yearly data into individual tables for each month.
What is the function string to do that?
What I dont know how to do is make the monthly total table "JANUARY TOTALS" factor in the date, month name, or numeric month name.
I want only the totals for each month. What do I need to add into the function to make it limit to only line items from January?
This is my current function string:
=SUMIF(2014::Category,2014 Totals by Category::A3,Amount)
I hope that was explained clearly enough.
I'd appreciate any help
James,
to get a summary by month you really need to use sumifs() which sums a column based on multiple conditions being true.
In your case you want to sum if:
- the date is greater than, or equal to, the first of the month AND
- the date is less than the first day of the next month AND
- the category matches
you can enter the desired month and year in the summary table, then duplicate the table to and update the month
James,
to get a summary by month you really need to use sumifs() which sums a column based on multiple conditions being true.
In your case you want to sum if:
- the date is greater than, or equal to, the first of the month AND
- the date is less than the first day of the next month AND
- the category matches
you can enter the desired month and year in the summary table, then duplicate the table to and update the month
something like this may work for you:
I would place these tables in the same sheet (Titled by year). Therefore all the dates in the entries in the Expenses table are from the same year (although they don't have to be).
The summary tables first row is a header row.
Enter the categories in column A and set up the header as shown
Cell A1 enter the year you want to summarize
B3=SUMIFS(Expenses::$E, Expenses::$B, $A3, Expenses::$A, ">="&DATE($A$1, COLUMN()−1, 1), Expenses::$A, "<="&EOMONTH(DATE($A$1, COLUMN()−1, 1),0))
this is shorthand for... select cell B3 then type (or copy and paste from here) the formula:
=SUMIFS(Expenses::$E, Expenses::$B, $A3, Expenses::$A, ">="&DATE($A$1, COLUMN()−1, 1), Expenses::$A, "<="&EOMONTH(DATE($A$1, COLUMN()−1, 1),0))
select cell B3, copy
select cells B3 thru the end of column M, paste (how many rows are in table depend on how many categories you have)
Thank you very much Wayne.
I appreciate all you have done. However I don't really understand the function, and I'd like to so I can modify it if need be or use something like it in the future. Could you perhaps break this down a bit more, explaining the bold part of the function, it works, I just dont understand the syntax and meaning for the syntax, and what it does and how it does. Also, I'm a bit confused with what you mean by Shorthand, as the two function strings you listed are the same?
=SUMIFS(Expenses::$E, Expenses::$B, $A3, Expenses::$A,">="&DATE($A$1, COLUMN()−1, 1), Expenses::$A, "<="&EOMONTH(DATE($A$1, COLUMN()−1, 1),0))
I understand that the $A$1 is just referencing Row 1 / Column A - which is the year (2013). I know, but dont understand there is a date range between the start of the month and the end of the month going on, but I can't figure out what the "(cell)−1, 1)," and "COLUMN(cell)−1, 1)," parts are. Can you explain the logic here?
Hello
The following sample tables are along your original scheme using month name to filter the data.
2014 (excerpt) A1 month A2 =MONTHNAME(MONTH(B2)) A3 =MONTHNAME(MONTH(B3)) A4 =MONTHNAME(MONTH(B4)) B1 date B2 2013-01-15 B3 2013-01-20 B4 2013-01-27 C1 category C2 A C3 B C4 C D1 amount D2 100 D3 50 D4 20
January A1 category A2 A A3 B A4 C A5 D A6 E A7 F A8 G A9 H B1 totals B2 =SUMIFS(2014::D,2014::A,C$1,2014::C,A2) B3 =SUMIFS(2014::D,2014::A,C$1,2014::C,A3) B4 =SUMIFS(2014::D,2014::A,C$1,2014::C,A4) B5 =SUMIFS(2014::D,2014::A,C$1,2014::C,A5) B6 =SUMIFS(2014::D,2014::A,C$1,2014::C,A6) B7 =SUMIFS(2014::D,2014::A,C$1,2014::C,A7) B8 =SUMIFS(2014::D,2014::A,C$1,2014::C,A8) B9 =SUMIFS(2014::D,2014::A,C$1,2014::C,A9) C1 January C2 C3 C4 C5 C6 C7 C8 C9
Notes.
Formula in January::B2 can be filled down across B2:B9.
The target month name is defined in January::C1.
February table is the same as January table except for the value in C1.
---
And the following sample tables are using date per se instead of month name to filter the data. In this scheme, you don't need month column in source table but the retrieving formulae in destination table become more complex.
2014 (excerpt) A1 date A2 2013-01-15 A3 2013-01-20 A4 2013-01-27 B1 category B2 A B3 B B4 C C1 amount C2 100 C3 50 C4 20
January A1 category A2 A A3 B A4 C A5 D A6 E A7 F A8 G A9 H B1 totals B2 =SUMIFS(2014::C,2014::B,A2,2014::A,">="&EOMONTH(C$1,-1)+1,2014::A,"<="&EOMONTH(C$1,0)) B3 =SUMIFS(2014::C,2014::B,A3,2014::A,">="&EOMONTH(C$1,-1)+1,2014::A,"<="&EOMONTH(C$1,0)) B4 =SUMIFS(2014::C,2014::B,A4,2014::A,">="&EOMONTH(C$1,-1)+1,2014::A,"<="&EOMONTH(C$1,0)) B5 =SUMIFS(2014::C,2014::B,A5,2014::A,">="&EOMONTH(C$1,-1)+1,2014::A,"<="&EOMONTH(C$1,0)) B6 =SUMIFS(2014::C,2014::B,A6,2014::A,">="&EOMONTH(C$1,-1)+1,2014::A,"<="&EOMONTH(C$1,0)) B7 =SUMIFS(2014::C,2014::B,A7,2014::A,">="&EOMONTH(C$1,-1)+1,2014::A,"<="&EOMONTH(C$1,0)) B8 =SUMIFS(2014::C,2014::B,A8,2014::A,">="&EOMONTH(C$1,-1)+1,2014::A,"<="&EOMONTH(C$1,0)) B9 =SUMIFS(2014::C,2014::B,A9,2014::A,">="&EOMONTH(C$1,-1)+1,2014::A,"<="&EOMONTH(C$1,0)) C1 2013-01-01 C2 C3 C4 C5 C6 C7 C8 C9
Notes.
Formula in January::B2 can be filled down across January::B2:B9.
The target month is defined in January::C1, which can be any date in target month, e.g., 2013-01-01, 2013-01-20, etc. The formulae in B will retrieve data with date in range: 2013-01-01 <= [date] <= 2013-01-31.
February table is the same as January table except for the value in C1.
Tables are built in Numbers v2.
Hope this may help,
H
EDIT: Replaced the last table with the correct one. (Formulae in B are correct)
Oops. The last table should have been as follows.
January A1 category A2 A A3 B A4 C A5 D A6 E A7 F A8 G A9 H B1 totals B2 =SUMIFS(2014::C,2014::B,A2,2014::A,">="&EOMONTH(C$1,-1)+1,2014::A,"<"&EOMONTH(C$1,0)+1) B3 =SUMIFS(2014::C,2014::B,A3,2014::A,">="&EOMONTH(C$1,-1)+1,2014::A,"<"&EOMONTH(C$1,0)+1) B4 =SUMIFS(2014::C,2014::B,A4,2014::A,">="&EOMONTH(C$1,-1)+1,2014::A,"<"&EOMONTH(C$1,0)+1) B5 =SUMIFS(2014::C,2014::B,A5,2014::A,">="&EOMONTH(C$1,-1)+1,2014::A,"<"&EOMONTH(C$1,0)+1) B6 =SUMIFS(2014::C,2014::B,A6,2014::A,">="&EOMONTH(C$1,-1)+1,2014::A,"<"&EOMONTH(C$1,0)+1) B7 =SUMIFS(2014::C,2014::B,A7,2014::A,">="&EOMONTH(C$1,-1)+1,2014::A,"<"&EOMONTH(C$1,0)+1) B8 =SUMIFS(2014::C,2014::B,A8,2014::A,">="&EOMONTH(C$1,-1)+1,2014::A,"<"&EOMONTH(C$1,0)+1) B9 =SUMIFS(2014::C,2014::B,A9,2014::A,">="&EOMONTH(C$1,-1)+1,2014::A,"<"&EOMONTH(C$1,0)+1) C1 2013-01-01 C2 C3 C4 C5 C6 C7 C8 C9
Notes.
The formulae in B will retrieve data with date in range: 2013-01-01 <= [date] < 2013-02-01.
* Previous formulae may fail to include the date of last day of month (e.g., 2013-01-31) depending upon the time part of source date. (If time = 00:00:00, there's no problem. But if time > 00:00:00, the previous formulae miss the last date)
Regards,
H
I am including a screenshot showing the result of the "column()" function for reference:
when you type the equal sign in a cell you can look at the function definitions on the very right:
So... now that you know how to see the meaning of functions and how to use them you have one more tool. You can search for functions by typing the name in the "search" field (top right). You can also click the groups to see specific kinds of functions (on the left). When click a function (on the right, "sumifs" is highlighted) you get an explanation of the function.
The summits() function contains arguments as follows:
The SUMIFS function returns the sum of the cells in a collection where the test values meet the given conditions.
SUMIFS(sum-values, test-values, condition, test-values…, condition…)
Function to list the month from a date range?
