How To Get Monthly Totals From a Numbers Table

Hi, this is Gary with MacMost.com. Let's take a look at getting date range totals in Numbers.

A vaguely common question I get is how do you total up something for a given date range, like say a month. For instance, here is a table with expenses in it. You can see the different dates here on the left. There's a category and there's an amount. What if you wanted to have a total for all the expenses in January? Getting a total for the entire table doesn't work and you don't want to divide the table up per month, that makes it hard to do calculations over the course of a year or doing calculations based on the expense category, things like that. So how can you easily get, say, the total for a given month. 

Well, a simple way to do it if you just need a quick answer is to select the range, right here. I'm going to select all the expenses for January 2023. At the bottom you'll see a handy little Sum there. But let's say you want to do it for all of these. Well, you can do that using a bunch of different methods. Let's start with one that uses a second table and some formulas. 

So here I've created a new table and I've setup a column for Month and a column for the total for that month. Let's start by populating this first cell with the first month. I'm going to type it like that and then I'm going to autofill it by selecting it and clicking that little yellow dot there and dragging it down. I've just enough cells here to get through the end of the next year, which is how many dates I've got. So now all I need here is the total. Notice these dates here if I look at the actual values it's the first of each month, January 1st, 2023, February 1st, 2023 and so on. It is just that the format for the cell is set to just show the month and year, like that. So, let's do the total. The function that will do it is Sums If. This will get the sum based on various criteria we're going to use. In this case does the year match and does the month match. So I'm going to do Equals and then type SUMIFS and the first part of SUMIFS is different than the SUMIF, without the S. That's the column we want to use for the totals, which is C. So we put that in there first. Those are the values. Now we want to have the first condition. Both the thing to test and the thing to match it to. So the thing we're going to test here is Column A, the dates. What are we going to match it to? Well we want to match it to the year. So we don't want to just use the date by itself, we want to put a year function around it. So I'm going to actually type it like this, year with parentheses surrounding the column. So just look for the year and then I'm going to compare that to the year, again using that year function there, that extracts the year from the date. I'm going to say this particular cell. So, do the years match. 

Now we want a second set of conditions. This is going to be Month. Let's extract the month from Column A and compare that to the month extracted from this cell. So do the month's match. One more parentheses to close the entire thing and a Return and you can see I get the value there. Everything where the month and year match in this column adding up these. If I were to take this cell, copy it and paste it throughout here, I'm going to double click on B to select all the cells except the Header, and Command V to paste will give me all of my answers here. So I can easily see how much I spent in April, how much in August, and so on. All of using formulas all pasted down through these cells like this. 

Now there are actually two ways to do this that don't require you to use formulas at all. The first one is to use Categories. Categories is something that has been around in Numbers for a long time. It allows you to make sense of a table like this very easily. So what I'm going to do here is with the Tables selected I can initiate Categories a lot of different ways. 

One is to use organize here and I can switch it On. I can set it to Show Groups For and I'm going to use, instead of Category, the Date. So Date there. You can see how it takes the column headings there. By: Year-Month. So group everything according by the Date, Year and Month. I'm going to turn it On. You can see now it groups all of January together, all of February, March, and so on. I can click here to Collapse all of the items for that month. I can hold the Option Key down and click and it will collapse all of them. Options Click will Expand all of them. Now I want the Totals. So here under Amount, if I click here you'll see this little control. I can click that and it is set to No Summary. Instead I want to have it Sum Up everything here. You can see there's the Total. I appears for every group. So now I will Option Click here and I can see all those sums like that. If at any time I want to examine, say, May 2023 I can expand it to see all the individual items there. I can turn Off Categories to go back to the regular table. Add more to the bottom and then easily turn Categories back On to get the summary again. 

If you find these videos valuable consider joining the more than 2000 others that support MacMost at Patreon. You get exclusive content, course discounts, and more. You can read about it macmost.com/patreon. 

Another way to do this without using formulas at all is to use a simple Pivot Table. so with this table selected here I could go to Pivot Table here. But that will create a new sheet. I want to create this right next to the first table. So I'm going to go to Organize and Create Pivot Table. This gives me the option to do it on a current sheet. So I see the Pivot Table that it creates right there. On the right I see under Organize, there's Pivot Options. I want to use the Date and the Amount. So I'm going to check Date, check Amount. You can see here it is going to summarize everything by year, 2023, 2024. What I want to do is go down here, to where it shows Date, Click the i right there and say Group, instead of by year, By Year and Month. So now I get the totals for Year and Month, like that. I can really easily see what is going on here. I can easily add to this table and then Update the Pivot Table with this button right here to have new totals or even adding new months to the bottom of it. I can even add Category here and it is going to add those as columns. So now I can see, say, for March 2023. I've got this much for advertising, this much for consulting, and so on all the way across. Then at total here for the entire month. 

Now I won't spend much time on this but you can use the new Array and Lambda functions to do this as well. So in this case here, in this table, I'm using a pretty complex function here that's going to map everything from the date column to a new Array that's just the year and the month with using just the first day of the month there for the dates. Then it's going to look for unique values. So it will just have one January 2023, not one for each row. After Unique it is going to sort them so they are in order. Then I end up with all the dates here. I think a better way to do it is actually right here. This is using a Make Array function. It's using the Lambda with the row index taking the function edate and starting with January 1, 2023 and adding the row index minus one to it. So January 1, 2023, then adding one month for February, one month for March, and all of that. Then it is doing that for the number of rows minus one, not including the Header here, so it is basically just populating this with months. The advantage of this is if a month is skipped here it won't be skipped here. It will show up and will get a zero results. The actual total for each of these two tables is the same. It's starting with filter and it is filtering the expenses table by the date with the year, the month, and the 1st day and comparing that to this column. So each one of these cells is just getting the rows for that particular month. Then it's using subtotals to get the sum of all of those rows. That's all part of a Lambda function here that is inside of a map function and this one function here populates the cells below it just as these monthly ones populate the cells below it as well. So you end up with just two cells with formulas in them. These two populate all the rows below. 

But if you are not into the new Arrays and Lambda functions I get it. They are much more complex. That's why I talk about all the other simpler options first. I'll include this demo document with this post at macmost.com so you can take a look at all of these different options. Hope you found this useful. Thanks for watching.