Displaying Sums In Numbers That Match the Filter

When you filter rows in Numbers, any totals or other footer calculations will still be based on all of the rows, not just the ones visible. You can change that by using functions like SUMIF that match the filter. But then you need to adjust your formulas and filters every time so they are the same. However, you can use a hidden column and a single-cell separate table to make this quick, easy and useful.


Check out Displaying Sums In Numbers That Match the Filter at YouTube for closed captioning and more options.
Video Transcript: Hi, this is Gary with MacMost.com. Today I'm going to show you how to build a filtered table in Numbers where the total reflects only the visible rows.
MacMost is brought to you by a community of more than 350 supporters. Find out how you can become a part of it at macmost.com/patreon.
So I've been asked about this a few times recently. When you filter a table in Numbers it eliminates some rows. But if you have a sum at the bottom in a footer row then the sum is still the total of all the rows. Ones that are visible and the ones that aren't. How do you get that sum to only reflect the rows visible after you apply the filter?
This table has some expenses in it and they're different types. Travel, Office, and Marketing. At the bottom here there is a sum. This is a simple formula. Just the sum of this column. So if I were to go and select the table and then go to Organize, Filter, and then add a filter where I set type is say travel, I will filter this table and only show travel expenses. But the total here is the total of all the expenses. Not just the travel ones.
So what we can do is we can use Sum If instead of sum. So the Sum If formula takes three parameters. The first one being what we're testing. So this column here. Then the value of it. So we can say, okay only travel. Then the values to total up. So this column here. That gives us the sum of all of the travel expenses. So that plus a filter gives us the correct answer. But then every time we want to change that we need to change both the filter and the Sum If formula to match. So we need to get clever about it.
What we're going to do is create another column. We're going to use that column to control both the sum and the filter. So we'll create a new column and we're going to do a simple formula here. I'll put the equals key to indicate I'm starting a formula. Then we're going to compare this cell here equal to travel in quotes. It's going to give me a true there and I'm going to copy and paste that in this entire column here. You can see I get True's and False's depending upon if this is travel. Now I can go and change the sum here to instead of checking to see if the type is travel, I can change it so instead of type it's just going to check this special column and it's going check it for the value of True. You can see it does the same thing. All of the rows that have a true in them are going to be counted in in the sum here.
So if I were to go in and change Travel to Office and then copy and paste that I would get the total there. In addition I can now go to Organize, Filter and add a filter for this special column. Say that I wanted it to be when the text is True and now it will only show me the rows that are True. So now the filter matches the sum. This is still far less than ideal because every time you want to change what you're filtering you have to go and change that formula in that special column and then paste it throughout all of the rows.
So what I'm going to do next is I'm going to create a new table, just a plain table, and I'm going to shrink it down to one cell by one cell. I'm going to name this table Filter and I'm going to place in it a type. So I'm going to start with Travel here. Now I'm going to change the formula here and instead of matching a type there in quotes I'm going to actually have it match this cell. I'm going to make sure that I preserved the location so that it's always referring to just this cell no matter which row I paste it in. So now if I copy and paste it in here it's going to match the type with the word in here, Travel, and I get True's next to the Travel ones. If I change it to Office, it changes to match those.
Now notice what happens to the sum. The sum is changing depending upon that because the sum is dependent upon whether these are True or False. So this matches this, sets this to True, and the sum reflects that. Not only that, but so does the filter. If I turn the filter back on for this table it's still going to match to True. So now all I need to do is change this and the filter will change so it only shows the True rows. So the sum now matches the filter.
So now I'm going to do a couple of extra things to make the spreadsheet really nice. The first thing I want to do is I want to make this cell a pop-up cell. So I'll change the cell data format to Pop-Up Menu. It already has the word Office in there. I'll add Travel and Marketing and I'm going to add one called All. You can drag these around. So I'm going to put All at the top and I'm going to put Marketing after that to alphabetize them. So now instead of having to retype it I can simply select and the filter and sum will reflect the difference.
But what doesn't work is when I select All. I have to build that in. So I'm going to go and temporarily turn off the filters for the table and change this formula to be an OR formula. So Or functions are going to surround this and if this is True, my original test is True we want it to show True, but also if this here, and we want to preserve row and column, is equal to All then it's also going to be True. So now I can copy and paste here and if I were to change this to All you can see they're all True. So if I go back to this table here and turn the filter back on they will all remain there. If I change this to Marketing you can see it only shows the marketing ones.
Now I can go and Hide this column and it all seems to work like magic. Go to Marketing, Office, Travel, and All. I get my total that matches the filter.
Related Posts:
Printable Mac Keyboard Shortcut Page For macOS Mojave ― Can I Get Subtotals On Numbers Smart Categories To Update When I Apply a Filter? ― How Do I Get Thumbnails In Numbers? ― How Do I Insert a Page Break In Numbers?

Download the example: FilterSums.numbers.zip