Dear fellows,
I have a large list of data (expenses chart for my business). There are several columns: type of expense, date, amount and source (cash/bank account). I need to summarise expenses of each king for every month. Is there an apportunity to do this automatically?
*I know that similar function in excel called pivot table report.
Your suggestions are highly appreciated!
Andrey.
OS X Mountain Lion (10.8.5)
Hi Andrey,
Check the Function Browser for the SUMIF and SUMIFS functions. For an example of the use of SUMIF, see the Personal Budget template supplied with Numbers 3.
SUMIF sums numbers in a column, selecting only those values whose labels, in a second column, match a stated condition (eg. the type of expense), See the Personal Budget template, supplied with Numbers 3, for an example using SUMIF.
SUMIFS does more or less the same thing, but allows setting of more than one condition. All onditions must be met to include a value in the SUM.
Regards,
Barry
Hi Barry,
Thanks for the great help!
I have managed to sum up my sales by categories and now I'm trying to add one more condition and see the results for every month. I have my transactions listed by date, not by months (this seemed to be a problem there) and don't understand how to create such a formula. I've created an idea how it might look like, but it gives me 0 as a result. Please have a look on the screen shot.
And one more question. There was a feature in Numbers'09 allowing you to break your data by categories. I can't find it in current version of Numbers. Is it still available? It might be very!!! helpful.
Andrey.
Hi Andrey,
Your formula has the correct syntax, but is too limiting in its second condition.
SUMIFS takes the range of values to be summed first (Expenses), and requires only the column name in this case as that is the only column with this name in the whole document.
Following that are one or more pairs of arguments in the form test-values,conditon
Your first pair says the value in column B of Table 1 must be equal to the value in cell A2 (or 'this table'). No apparent problem there. A2 contains the text value "IT", and this value is found in several cells in column B of Table 1.
As you will be filling this formula to the right to pick up data for each month, you should make the referencess to Expenses, Table 1 column B and A2 all absolute references with respect to the column part. (In numbers 3, the term is "Preserve column." The row reference in A2 should be left as a relative reference. Filled down to the next row, you want the item to be the one in row 3.
The next test-values, condition pair is wht's causing your zero result. Each of the displayed dates in column C of Table 1 is a date and time value marking a specific time of day (midnight—00:00:00) on a specific date). Your condition is that this Date (and time) must be equal to the value in B1 of 'this table' for the value in column B of table 1 to be included in the sum.
The first requirement is the the value in B1 be an actual Date and Time value. If you entered a date into the cell, that will be true. If you entered only the month and year, it's still likely that umbers has filled in the blanks and created a full Date and Time value of midnight at the beginning of the first day of that month. You can check this by clicking on the cell and noting what appears in the cell or th entry box. You should see at least the full date, including year, month and day.
Even with an actual date and time value in the cell, though, the only rows that will match this condition will be those containing the same Date (and the same time of day). The condition is too restrictive. You want to include he values from all days in the month—those that occur on or after midnight at the beginning of April 1 up to those that occur just before midnight at the end of that April. The formula will require two conditons, one establishing the beginning of the time period, the other establishing the end of that period. Because the comparison operator for each of these will not be "equal to", it will need to be stated explicitly, and must be presented as a text string.
test-values for each of these will be theDate and Time values in column C of Table 1. The comparisons will be with the value in Row 1 of the current column (B) for the second pair, and with a date and time value constructed from that value for the third.
Pair 2: Table 1::$C,">="&B$1
Pair 3: Table 1::$C,"<="&EOMONTH(B$1,0)
These require that the actual date in B1 be the first of the named month. The day does not need to be displayed.
with all parts put together, the formula will look like this:
=SUMIFS(Table 1::$D,Table 1::$B,$A2,Table 1::$C,">="&B$1,Table 1::$C,"<="&EOMONTH(B$1,0))
Entered in B2, the formula may be filled down to B5 and right to column E (or beyond).
Regards,
Barry
(Note: Not tested. Typos are possible.
Barry,
its all clear now! thank you very very much!!!
Barry,
despite your exhaustive answer totaly answered my initial question, let me ask you another one thing.
I've seen video tutorials showing the simplest way to group expenses (in case of my table) by categories (column B in my case). In Numbers'09 you just select the column, click to drop down the column menu and select Categorize by This Column. And that's it.
I have receintly updated my Numbers version up to the latest one and I can't see this option available here. Browsing help by "categories" or "categorizing columns" doest'n lead to any relevant answer as well.
Does this feature still exist, or not any more? Please advise.
Many thanks for you kind help!
Andrey.
Hi Andrey,
Categories has gone in Numbers 3, but you are on the right track! See this reply from tomogden
That reply explains the SUMIFS, the COUNTIFS and the AVERAGEIFS functions and others like them to build Excel-like pivot tables.
Regards,
Ian
Looking for an analogue to Excel's PivotTable report in Numbers
