Compact a Table by any Field: Name, Department, Sales, etc with SUMIF

Summarize tables with SUMIF, one of the most loved Excel Functions.

Imagine you have a big list, maybe thousands of registers. You want to know:

  • The sales each vendor did
  • The total length of a type of pipe
  • The sales by season
  • The debt each client has
  • And more…

Let’s see…

The table shown in graphic 1 is a simplification for the purpose of this explanation; it is a plain list of [Salesman] with the corresponding [Department] and the [Amount].

You want to know…

How much did each Salesman sell?

In other words; you want to turn this table…

Graphic 1

Into this other one…

Graphic 2

Excel SUMIF List

No Pivot Tables this time.

Well…

It is time to explore this Excel Function:

Use SUMIF to Sum Values that Match a Single Criterion

Imagine you have a pipe list (graphic 3). Your goal is to ask three different suppliers for quotation.

This list is short for the purpose of explanation; you may have thousands of entries.

Graphic 3

List to be Compacted with SUMIF

You want to summarize the total length of pipes by [Diameter] and by [Material]; after that, you will send a friendly email to suppliers with a summary like the one below (graphic 4):

Graphic 4

 

Compacted List with SUMIF before Entering Formula

Tip 1: Create a list with unique entries by deleting duplicates. How? Use Advanced Filter in Excel 2003 or a Table in Excel 2007.

You are ready to summarize the two tables (graphic 4) with Excel SUMIF.

Let’s start; look at the table “Summary by Diameter” below (graphic 5).

You want to sum the length if the diameter is 200 mm.

Look at B17 cell. This Function sums the range [Length] if its corresponding range [Diameter] matches with 200.

In Excel language it sums the range “sum_range” if its corresponding range “range” matches with “criteria”

Graphic 5

SUMIF formula

Then copy and paste the formula.

Tip 2: Copy and paste SUMIF formula with confidence. How? Use always Absolute Reference for the ranges “range” and “sum_range” arguments. Why? You usually copy and paste this formula

Graphic 6

Compacted List with SUMIF

Make the formula more readable and consistent with Names. Download this example with range names

Tip 3: Use range names. Why? The function becomes more readable and copying and pasting turns more intuitive.

Now…

Look at the table “Summary by Material” below (graphic 7).

You want to sum the length if the [Material] is PVC. How?

Now look at B27 cell.

Graphic 7

SUMIF Formula

Finally, copy and paste the formula downward. The summary is ready.

Graphic 8

List Summarized with SUMIF

Send the email to suppliers with Carbon Copy (Cc:) to your impatient boss.

Wait…

Take care that totals are consistent; they must be all 10,836.51 for this example.

Tip 4: Always cross check the total.