MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Summarize Data with Advanced Filter


April 11, 2018 - by Bill Jelen

Summarize Data with Advanced Filter

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.

Create a summary with 1 row per customer
Create a summary with 1 row per customer

This week will feature five different ways to solve the problem.


Watch Video

  • 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.
Advanced Filter
Advanced Filter
  • 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:

A summary by customer
A summary by customer

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