Hi there. Need to get the SUM of filtered data in Excel?
Let me help
Hey I'm Sharyn from Excel at Work where we offer straightforward non-technical
assistance to help you save time work smarter and
improve your Microsoft Office skills Right I'm going to take you through two
different options to get the SUM of only the filtered data in Excel and if
you're using my 100 plus Steps to Proficient workbook
to improve your Excel skills I've popped the topic number this video relates to
in the description box below. If you haven't yet claimed a copy of this
workbook I've also added a link to where you can
grab yourself a copy Right let's get your SUM working. These
tips are quick and simple so let's get to it. Right
let's pop some filtering on this data so I'm just going to come into here and
using Control + A I'm selecting the table I'm just going to whip up to the Data
tab and then pop on filter Okay, so what we'll do now is just come
down to the bottom here and maybe if we just pop in a little label
and I'll just do an Alt + equals to do my SUM and maybe just copy that
across here and there we go. Okay so let's just have
a little bit of a chat about this. You're probably watching this video because you
realize that if we come in here and we do a filter, let's just say we'll
filter on PA here, our totals don't update and it would be
lovely wouldn't it if we actually filtered this
and this total down here updated. It's such a simple
fix. Watch this. I'm just going to take out these sums so you can see we've got
sums up here and I'm going to delete them out now
with the filter on I am now going to do my Alt + equals now
If you're wondering what Alt equals is I'm just going to go to the
Home tab and it's the shortcut for your auto sum so you can see there it's
actually showing you Alt + equals. So I just love shortcuts so of
course if I just click that's exactly the same thing
Now check this out. We don't get the SUM function, we get something called the
SUBTOTAL function I'm going to press Enter and if I now go
and change this so I'm just going to come back and I'll go select
all and you're going to see that that total
gives me the total for everything. If I just go over the top of here
and we can just do a double check obviously we can see it there we can see
it down here as well So I'll now go up and I'll just go up
and grab our PA again and look see how it updates so the
SUBTOTAL function will be popped in in the place of the SUM
function and what it's doing obviously is it's updating and giving you a sum
only on the visible rows. So I can copy that
across and you'll see that it's exactly the same which is fantastic. Do check
that you've got everything okay So make sure that you have actually
covered the range for the filter so that's one way that you can do it
I'll just go and turn that off So actually I'll just clear the filter
In fact what I'll do is I'll take the filter off
So oh by the way if I just go Ctrl + A, Shift + Control + L and that will take your
filter off as well so if you like shortcuts
that's an easy way to do it as well. Okay so I'm just going to take this out
So that was option number one is just using your your Subtotals
Now here's option number two. I'm just going to again just use Control
A and that will select the entire table and what I'm going to do now
is I'm going to make my way over to the Insert tab
Now I don't often go to Insert and click onto the Table option. I use my Control + T
and you can see that's the shortcut there. So if I was to click onto
here it's the same as using Control + T and it'll just ask me hey do you want to
create a table over the top of this data, my table has headers, it's actually
saying that each row at the top of each column
is my heading row and of course that's fine so I'm clicking on
OK. Now a lot of people don't like this look. It's the default look that gets put
over the top of your data when you're using Tables. So you can if
you want to you can go and change it back
you know just back to no formatting and of course we look like it looks like it
did when we were working with filters But there are added advantages to using
Tables So if I just pop on the Total Row, so by
the way when you've got a Table you will see the
Table Design contextual tab If I click away you'll see it's gone you
need to be on the Table and it'll come back
and you need this, this Total Row. Now that that's on you can see over here the
word Total and as you click onto that Total Row at
the bottom of each column you will be given different options that
you can then pop in and you can see this one is
SUM, and look at that at the very top there it's actually
showing you that it's at the subtotal function
now of course I can, I can do that for each one if I want to. So I can just come
over here and but that's very long-winded so I can
just actually just drag it across and you'll see that it's actually given me
that Subtotal So easy. Now let's just go and check it
then so if i come up here and i do my PA,look at that
So that's two different ways that you can get the correct SUM for your
filtered data. Right let's just look at how you
can convert this Table back to a range if you want to
So I'm just going to go Convert to Range, Do you want to convert your Table to a
range Yes, and look at that your Subtotals have
remained in place. Okay I hope that helped.
So I really hope this instruction has been helpful and you are now
able to quickly get the SUM of filtered data in Excel
If this has been helpful please let me know in the comments below
and if you use Excel and want to stay connected
please consider Liking, Following Subscribing, because I'm sharing new
videos regularly And if you know someone who would
benefit from this help please be sure to share this video with them
Hey, thanks for watching and have a fabulous day.