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

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.