# How to Use Excel SUMIF to Sum Values that Meet One Criterion

You usually have a big list, maybe thousands of registers; it is a common requirement to summarize it by any field, for example, you want to know:

- The sales by vendor
- The total length of an product by its code
- The sales by season
- The sales between a date period
- And more…

SUMIF can help you; let’s see…

To illustrate how SUMIF works, look at the table below (graphic 1)…

You have a question: how much did each Salesman sell? So you want to turn this table...

## Graphic 1

Into this tiny one (graphic 2)...

## Graphic 2

No need to use Pivot Tables this time.

Well…

Let’s explore this loved Excel Function in more detail…

## Use Excel SUMIF to Sum Values that equal one Criterion

Imagine you need to make a big purchase for your project so you want quotations from local suppliers; the items to buy are pipes.

You
won’t send a 1000 rows pipe list so that each supplier makes the computations;
you can send a professional email with a compact table.

The pipe list is in graphic 3; it is a short version for the purpose of explanation; you will have hundreds or thousands of entries.

## Graphic 3

The Excel attachment for the email must look like the tables shown in graphic 4. In other words, you will have the pipes total length by [Diameter] and the pipes total length by [Material];

## Graphic 4

Here’s how to do it…

Look at B17 cell (graphic 5); the SUMIF formula summarizes the pipes by diameter; look at the table "Summary by Diameter" below. This Function sums the range [Length] if its corresponding range [Diameter] matches with 200.

The criteria can be more complex: you can use wildcards (*, ?), logic operators (<, >, etc.)

## Graphic 5

Then copy and paste the formula downward.

**Tip 1: Copy and paste SUMIF
formula with confidence with Absolute Reference for the ranges “range” and
“sum_range” arguments.**

The “Summary by Diameter” table is ready, see graphic 6

## Graphic 6

Now, finish your summary…

Look at the table
"Summary by Material" below (graphic 7). You want to sum the length
if the [Material] is PVC.

## Graphic 7

Copy and paste the formula. The “Summary by Material” table is ready; see graphic 8.

The report is done, attach the Excel file and send the email to suppliers with Carbon
Copy (Cc:) to your **patient** boss.

Remember always to cross check; totals must be consistent. They must be all 10,836.51 for this example.

**Tip 2: for eternity
cross check the total.**

## Graphic 8

## Resources

- Use SUMIF to Compact Large Tables by a Single Field

Compact a Table by any Field: Name, Department, Sales with SUMIF. Make an Executive Report Ready to Email to your Boss... - Download this SUMIF example

## Excel Books Worth Reading

## Comments 2 comments

Thank you. What an awesome little function. :-)

FYI, I found you based on the following google search: sum based on flags in excel

First, thank you for this guide. It is much appreciated. Could you tell me which version of excel you are using here? I am currently deciding which version i want to purchase (it isnt all that cheap after all), so i am looking through different guides (Sumif, lookup, and a few others) to get an idea whether or not a new version is worth it. So far i foudn a gudie for excel 2010 ( http://www.excel-aid.com/the-excel-sumif-function.... ), which seems pretty similiar to yours (i mean the screenshots of excel, not the style). But in some ways it is a little bit different. Do you maybe use a mac version of excel?