Summarizing Data in Excel Using Subtotals
This is the first of a five-part series on Summarizing Data
This week, I will cover Subtotals, Remove Duplicates, Advanced Filter, Consolidate, & Pivot Tables
How to summarize with Advanced Filter
1. Copy the headings to an Output Range
2. Select the data in the customer column.
3. Data, Filter, Advanced. Copy to another Location. Specify J1 as the output. Choose Unique Items Only. OK.
4. Select K2:M17. Type a SUMIF formula. Ctrl+Enter
This is the first of a five-part series on Summarizing Data
This week, I will cover Subtotals, Remove Duplicates, Advanced Filter, Consolidate, & Pivot Tables
How to summarize with Advanced Filter
1. Copy the headings to an Output Range
2. Select the data in the customer column.
3. Data, Filter, Advanced. Copy to another Location. Specify J1 as the output. Choose Unique Items Only. OK.
4. Select K2:M17. Type a SUMIF formula. Ctrl+Enter
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2189: Summarize with Advanced Filter.
Hey, welcome back to the MrExcel netcast, this is Summarizing Data week.
This is our third method; we're trying to create a summary of 1 row per Customer.
So far, we used Subtotals and Remove Duplicates.
I'm going to go old school here: Copy these headings over to an output range and use Advanced Filter.
Now, my Advanced Filter is just going to be on column D like this.
So that was Ctrl+Shift+Down Arrow, choose Advanced, Copy to another location-- where am I going to copy it to?
That heading in J. And then, right here, "Unique records only".
This was long before we had to Remove Duplicates, would get us a unique list of customers like that.
Alright, now that we have this list of customers we have to build a formula.
Yesterday, I used SUMIFS, today I'll try SUMIF.
And what we do is, we say we're going to look through all those Customers over in column D. So, from here, Ctrl+Shift+Down Arrow, press F4 to put the dollar signs in; comma, then the criteria is here in J2-- click on J2-- press F4-- 1, 2-- 3 times; single dollar sign before the J; and then, finally, where are the numbers coming from?
Well, they're coming from the quantity column-- so, here, Ctrl+Shift+Doen Arrow, and I'll press F4-- 1-- 2 times to lock it down to just the columns like that; and then Ctrl+Enter.
Once we have that first formula in, drag across, double-click the Fill handle to shoot it down, and there's our results.
All these methods are in this new book, MrExcel LIVe, The 54 Greatest Tips of All Time.
Click that "I" on the top right-hand corner to check out more about the book.
This week-- all this week-- we're doing a series on Summarizing Data.
So far, we've done Subtotals, Remove Duplicates, today Advanced Filter.
How to summarize with Advanced Filter: Copy the headings to an output range; select Data in the Customer column; Data, Filter, Advanced, Copy to another location; specify that Customer heading as the output; "Unique items only"; click OK; then it's a simple SUMIF formula; and copy that down.
Hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast, this is Summarizing Data week.
This is our third method; we're trying to create a summary of 1 row per Customer.
So far, we used Subtotals and Remove Duplicates.
I'm going to go old school here: Copy these headings over to an output range and use Advanced Filter.
Now, my Advanced Filter is just going to be on column D like this.
So that was Ctrl+Shift+Down Arrow, choose Advanced, Copy to another location-- where am I going to copy it to?
That heading in J. And then, right here, "Unique records only".
This was long before we had to Remove Duplicates, would get us a unique list of customers like that.
Alright, now that we have this list of customers we have to build a formula.
Yesterday, I used SUMIFS, today I'll try SUMIF.
And what we do is, we say we're going to look through all those Customers over in column D. So, from here, Ctrl+Shift+Down Arrow, press F4 to put the dollar signs in; comma, then the criteria is here in J2-- click on J2-- press F4-- 1, 2-- 3 times; single dollar sign before the J; and then, finally, where are the numbers coming from?
Well, they're coming from the quantity column-- so, here, Ctrl+Shift+Doen Arrow, and I'll press F4-- 1-- 2 times to lock it down to just the columns like that; and then Ctrl+Enter.
Once we have that first formula in, drag across, double-click the Fill handle to shoot it down, and there's our results.
All these methods are in this new book, MrExcel LIVe, The 54 Greatest Tips of All Time.
Click that "I" on the top right-hand corner to check out more about the book.
This week-- all this week-- we're doing a series on Summarizing Data.
So far, we've done Subtotals, Remove Duplicates, today Advanced Filter.
How to summarize with Advanced Filter: Copy the headings to an output range; select Data in the Customer column; Data, Filter, Advanced, Copy to another location; specify that Customer heading as the output; "Unique items only"; click OK; then it's a simple SUMIF formula; and copy that down.
Hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
