Summarize Data with Advanced Filter
April 11, 2018 - by Bill Jelen
Your manager needs a summary of total revenue, cost, and profit for each customer in a large data set. Today I look at Advanced Filter and SUMIF to solve the problem.
The goal is to summarize this 563-row data set so you have one row per customer.
This week will feature five different ways to solve the problem.
- Monday: Summarize Data with Subtotals
- Tuesday: Summarize Data with Remove Duplicates
- Today: Summarize Data with Advanced Filter
- Thursday: Summarize Data with Consolidate
- Friday: Summarize Data with Pivot Tables
- Saturday: Summary of Summarize Data Week
- Copy the headings from D1:H1 and paste to J1. The J1 will become the Output range. K1:M1 headings will be used later.
- Select D1. Press Ctrl + Shift + Down Arrow to select to the end of the data.
- Select, Data, Advanced Filter. Choose Copy to Another Location. The List Range is correct. Click in Copy To: and click on J1. Choose the box for Unique Items Only. Click OK.
- Select K2. Ctrl + Shift + Down Arrow and Ctrl + Shift + Right Arrow to select all of the numbers.
Enter a formula of
=SUMIF($D$2:$D$564,$J2,E$2:E$564). Press Ctrl + Enter to fill the selection with a similar formula
After 37 clicks, you have this result:
This method is similar to Adam's method from Tuesday. Entering the formula requires a lot of keystrokes. Tomorrow, an ancient method that will dramatically reduce keystrokes.
This week is Summarizing Data week. Each day, we will look at five different ways to solve a problem.
Excel Thought Of the Day
I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:
“Excel is the second best software in the world” [for anything]
Title Photo: Nathan Numlao / Unsplash