How To Use SUMIF & SUMIFS In Excel

Here are the differences and similarities in using the SUMIF & SUMIFS functions in Excel:

You can download the file here to follow along. If you get a preview, look for the download arrow in they upper right hand corner.

SUMIF & SUMIFS are great tools for adding up items based on certain criteria. Here is the syntax for each:

si1

Besides the obvious fact that SUMIFS provides for more than one criteria, in the SUMIF function, the data that you are summing is added at the end, while in SUMIFS, that’s defined up front. For this reason, I think that SUMIFS is more logical to use, even for one criteria, since it is structured as the name suggests, SUM first, then the IFS.

Here we have a table with our sales for several salesmen over 6 months and whether they were over or under their targets:

si2

Using the SUMIF function, I want to determine what Tom’s sales were. Since we are using a table, the data ranges in our formula are defined by the heading names. So, the formula for summing the sales for Tom is:

=SUMIF(SUMIFTable[Salesman],A1,SUMIFTable[Sales])

First we selected the criteria range (Salesman), then the criteria (“Tom” as indicated in cell A1), and finally the summing range (Sales), and we get our answer 247,331. To double check, we’ll filter our table using the Table Slicers (You can find the post regarding Table Slicers here) and we get the same answer:

si3

Now, let’s use the SUMIFS function to determine Bob’s Sales for March and just when he was Under target. The formula for that will be:

=SUMIFS(SUMIFTable[Sales],SUMIFTable[Salesman],A3,SUMIFTable[Month],B3,SUMIFTable[Target],C3)

For SUMIFS, first we select the Sum Range (Sales), then our first criteria range (Salesman) and that criteria (Bob in A3), then our second criteria range (Month) and that criteria (Mar in B3), and finally our third criteria range (Target) and that criteria (Under), and we come up with 18,499, confirmed by the filtered table:

si4

You can use the SUMIFS function even if you only have one criteria, and many feel that’s practical, since, as mentioned above, the structure of the function is more logical. To use the SUMIFS function for our first example, the formula would be:

=SUMIFS(SUMIFTable[Sales],SUMIFTable[Salesman],A1)

And, as you can see, the results are the same as when we used SUMIF:

si5

There you have it. SUMIF vs. SUMIFS. I hoped it was valuable to you and please feel free to drop a comment below.

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , ,

4 Responses to How To Use SUMIF & SUMIFS In Excel

  1. Meni Porat February 19, 2014 at 7:38 am #

    Hi Michael,

    I have a question concerning the SUMIF function:
    Suppose you want to sum the sales of all the salesmen whose name length is greater than 3 (in your example: Chuck).
    Can this be accomplished using the SUMIF?

    • Michael Rempel February 19, 2014 at 9:39 am #

      Meni Porat – I could not come up with a way to incorporate the LEN function within a SUMIFS. One way would be to add a helper column that would give the length of the cells and SUMIF off of that column.

      Another way would be to use the SUMPRODUCT function. That formula in our example would be:
      =SUMPRODUCT((SUMIFTable[Sales])*(LEN(SUMIFTable[Salesman])>3))

      MAR

  2. Meni Porat February 21, 2014 at 3:19 pm #

    Hi Michael,

    My solution to the problem is by using an array formula:

    =SUM(IF(LEN(Table1[Salesman])>3,Table1[Sales]))

    Another 2 variations (using array formulae, assuming that the table’s
    range is: A8:D25):

    =SUM(IF(LEN($A$8:$A$25)>3,$C$8:$C$25))

    =SUMPRODUCT(IF(LEN($A$8:$A$25)>3,1,0),$C$8:$C$25)

    These last 2 variations are not as good as the first one, though, since they
    fail to use the dynamic character of an Excel table: If the table’s range changes, one has to change the formulae……

    Best Regards,
    Meni Porat

    Best Regards,
    Meni Porat

Trackbacks/Pingbacks

  1. Two Methods To Calculate The Average Sales Eliminating Outliers in Excel | Excel Bytes - October 26, 2015

    […] You can learn more about the SUMIFS function here. […]

Leave a Reply