Excel SUMIF and SUMIFS Formulas Explained

In this Microsoft Office Online Training article we’re going to explain how to use the SUMIF and SUMIFS Formulas, and look at a couple of different applications for them.

You can download the workbook used in this example and practice what you learn.

Learning the SUMIF formula extends the capabilities of the basic SUM formula by allowing you to tell Excel to only SUM items that meet a certain criteria.  Whilst the SUMIFS is new in Excel 2007, it allows you to stipulate multiple criteria, hence the plural.

Enough explanation, let’s dive into an example as it’s easier to visualise.

SUMIF Formula first:

The function wizard in Excel describes the SUMIF Formula as:

=SUMIF(range,criteria,sum_range)

Not very helpful is it?  Let’s translate it into English with an example.  In the table below we want to sum the total number of Units (in column D) for Dave:

Translated, our formula would read like this:

=SUMIF(the name in column C, = Dave, add the figures in column D)

We could even put a summary table at the bottom of the list for each builder like this:

Our formula in cell D12 would be:

=SUMIF(C2:C7,”Dave”,D2:D7)

While the above formula is good, if we were to copy it to the rest of the summary table (cells D11 to D14 and F11 to F14) we would have to manually change the cell references and builder’s name to get the correct answers.

The solution is to use absolute references to help speed up the process of copying the formula to the remainder of column D and column F.  With absolute references our formula would look like this:

=SUMIF($C$2:$C$7,$C12,D$2:D$7)

We could then copy and paste the formula to the remaining cells in our summary table without having to modify it at all.  As we copied it down column D, Excel would dynamically update the formula to automatically pick up the next builder in the list.  Then when we copied it across to column F, Excel would dynamically alter the reference to column D to F.

The best way to fully understand this conundrum is to try it for yourself.  Download the workbook used in this example here for practice.

Note: I used a basic example to illustrate how to use the SUMIF formula, but you can also achieve this using the subtotal tool in the Data tab.  But that’s a lesson for another day!

SUMIFS Formula:

The function wizard in Excel describes the SUMIF Formula as:

=SUMIFS(sum_range,critera_range_1,criteria_1,criteria_range_2,criteria_2…..and so on if required)

Extending the SUMIF example above, say we wanted to only summarise the data by builder, for jobs in the central region.  We could use the SUMIFS formula as it allows us to set more than one condition.

Here’s how the formula would be interpreted if we wanted to add up the Units in column D, for Doug’s jobs in the Central region:

=SUMIFS(add the units in column D if, in column C, they are for Doug and, if in column B, they are also for the Central region)

Note: Excel will only include the figures in column D in the sum when both conditions (Doug & Central) are met.

In Excel our formula would read:

=SUMIFS(D$2:D$7,$C$2:$C$7,$C18,$B$2:$B$7,$B$17)

Note: again I’ve used a simple example to illustrate this formula, but you could also achieve this summary table for each builder by region using Pivot Table. To watch a tutorial on how to insert a Pivot Table sign up to our Premium Microsoft Office Online Training or read our Pivot Table Tutorial.

Try other operators in your SUMIF and SUMIFS

Just like the IF formula, the SUMIF and SUMIFS are based on logic.  This means you can employ different tests other than the text matching (Doug & Central) we’ve used above.

Other operators you could use are:

• =             Equal to
• <             Less Than
• <=           Less than or equal to
• >=           Greater than or equal to
• <>           Less than or greater than

For example, if you wanted to sum units greater than 5 the formula would be:

=SUMIF($D$2:$D$7,”>5”,$D$2:$D$7)

Don’t forget to download the workbook used in this example and practice what you’ve learnt.

Then check out our other conditional logic articles IF Statements Explained and COUNTIF and COUNTIFS Formulas Explained.

Did you find this useful, or just confusing?  Let us have your feedback below.

Share This

Tweet

Please share this or leave a comment and I'll make sure you get a personal reply.

Tagged as: Excel SUMIF, Excel SUMIFS, Excel Training, Excel Tutorials