# 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…

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

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.

## More by this Author

Engineer Greg 7 years ago

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

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

marot90 2 years ago from Munich, Germany

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?