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

More by this Author


Comments 2 comments

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?

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working