earning 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 D11 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,$C11,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 Tables. 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)