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...

Into this tiny one (graphic 2)... 

No need to use Pivot Tables this time.

Well…

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

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.

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]; 

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.)

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

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.

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.