ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software»
  • Office Software Suites»
  • Microsoft Office

How to use the SUMIF Function in Excel

Updated on March 27, 2014

The SUMIF function in Excel is a powerful way to quickly summarize data without having to go through the hassle of making a pivot table, especially on reports that are run over and over again. In my job as an accountant, I use it at least several times a month to summarize data in reports that I am responsible to run. I have also found it very effective for summarizing data dumps from my bank and financial software.

SUMIF Function in Excel

Source

Using the SUMIF function is very easy once you understand how the formula works. The above picture shows the parameters that the formula is looking for. Let us explore each one in detail to obtain a better understanding of what Excel is looking for.

Range

The Range is a group of cells that you want to search for the specific criteria in. Think of it as a group of cells that make up a table that you want to look up specified values or text in. The Range is always made up of cell references. This field is a required part of the formula.

Criteria

The Criteria is simply what specified data that you want to look up in the Range and add together. It can be a number, text, cell reference, or a formula. This is also a required field. Note: If the Criteria is a number, you just have to enter the number. However, if it is text, or mathematical or logical symbol, it must have double quotation marks on each side of it. You can also use an asterisk (matches a sequence of characters) or a question mark (matches only one character). Examples of what Criteria could be are "Texas", "T*",">36", "<5", "Widgets", now(), C6, 36, etc.

Sum Range

The Sum Range is the range of cells that you actually want to add together. It can be the same as the Range, but many times it is a different column. If you choose leave out the Sum Range, Excel will add the values that are included in the Range.

Advantages of Using SUMIF in Excel

I think that there are many advantages to using the SUMIF Function in Excel over other methods of summarizing data. For one, they are much easier to automate because you can set the Range and Sum Ranges way down the page so that each new entry is automatically picked up and bucketed correctly. Second, there is not a need to have to constantly refresh the data as in the case of a pivot table. Another advantage is that you can choose to summarize data that is over a certain percentage or dollar amount. Finally, you could also add a drop down or combo box to drive what data is summarized kind of like a pivot table allows you to set a filter.

Limitations of Using SUMIF in Excel

The SUMIF function is easy to use and works for almost any application. The only limitation that I can think of is when something like a new salesperson starts or a new product is added. Then you would have to manually update the information that you are trying to summarize. For example, you would have to add a new salesperson to the Top Salesperson summary that was created in the example of below. Also, when using the SUMIF function, sometimes a little extra work is needed to set everything up. In the example below, I only entered five different products to summarize. In my professional experience, most of the time you would have many more products to summarize. In that scenario, it would probably be quicker to create a pivot table to summarize the data especially if this is a one time or quarterly type report. If it is a monthly report, I would probably still use the SUMIF function so that I did not have to keep refreshing the pivot table.

Examples of Using SUMIF in Excel

Now that you have an overview of how to use SUMIF in Excel, now let us look at a few practical examples. I created a spreadsheet with two different tabs, one for a sales register that houses detailed sales data and the other one for a sales dashboard to summarize the data. Below is a picture of the "Sales Register" tab that shows a detailed listing of sales for the year.

Source

I will demonstrate how to create a sales dashboard using only the SUMIF function to pull the relevant data rather than creating a pivot table, which would have to be refreshed each month. The SUMIF function will pull the data automatically.

Calculating Sales by Month Using the SUMIF Function

This example will demonstrate how to use the SUMIF function to calculate monthly sales from the sales register. Enter "Jan" into cell B5 on the "Dashboard" tab. Click and pull the small box in the lower right hand corner of the cell and drag it across to cell M5 so that it auto fills all of the months through December. Enter "YTD" into cell N5. Highlight cells D6:D45 and hit the F4 key to anchor the cell reference. The reason that I chose to end in D45 rather than D15 is that I am leaving room for future entries to be made into the sales register. If I were doing this for professionally, I would have at least went down to row 5000 or so. Next, enter a "," and then on the "Dashboard" tab, select cell B5 and then enter another ",". Finally, click back over to the "Sales Register" tab and highlight cells E6:E45. Hit the F4 key to anchor the reference and enter a ")". Copy the formula and paste it into cells C6 through M6. Finally, add a SUM formula in N6 to add up the year to date sales. See the example below:

Function Arguments Quick Reference Guide

Part of Formula
Enter
Comments
Range
D6:D45
Highlight cells D6:D45 on the "Sales Register" tab and hit F4 to anchor the cell reference.
Criteria
B5
Click on cell B5 on the "Dashboard" tab.
Sum Range
E6:E45
Highlight cells E6:E45 on the "Sales Register" tab and hit F4 to anchor the cell reference.
Tip: Another way to enter a SUMIF formula is to go to the "Formulas" tab in Excel and click on the small arrow below the Math & Trig button. A wizard window will open and ask for the above information.
Source

Calculating the Top Salesperson Using the SUMIF Function

This second example will demonstrate how to summarize the sales by salesperson using SUMIF. On the "Sales Register" tab, copy the salesperson names in cells A6:A11 and paste them on the "Dashboard" tab into cell B10. In cell D10 enter "=SUMIF(" and then highlight cells A6 through A45. Hit the F4 key to anchor the reference. Type "," and click on cell B10 and then type "," again. Next, highlight cells E6 through E45 and hit F4. Finally, enter ")". Copy the formula and paste it into cells D11 through D15. Lastly, added a total by using a SUM formula to add up the total sales.

Source

Calculating Sales by Product Using the SUMIF Function

This final example will demonstrate how to summarize sales by product using SUMIF. In the following cells on the "Dashboard" tab, enter the corresponding product:

  • Cell F10 - enter "Ball"
  • Cell F11 - enter "Box"
  • Cell F12 - enter "Triangle"
  • Cell F13 - enter "Widget"
  • Cell F14 - enter "Widget A"

In cell G10, enter "=SUMIF(" and then highlight cells C6 through C45 on the "Sales Register" tab. Hit the F4 key to anchor the reference. Next, enter a "," and then highlight cells E6 through E45 and then hit the F4 key. Finally, enter a ")". Copy the formula and paste it into cells F11 through F14.

Source

Below is a what the actual dashboard would look like:

Source

Learning to master the SUMIF function in Excel will make you more efficient. It is a great way to summarize specific data in a table and pull out only the amounts that you want. Feel free to contact me with any questions or comments.

Was this Hub helpful?

See results

© 2014 Eric Cramer

Comments

    0 of 8192 characters used
    Post Comment

    • Fiona Jean Mckay profile image

      Fiona 15 months ago from South Africa

      I will definitely be trying this one out at some stage - no projects on the go that need it right now but I'll think of something I am sure - I love playing around with Excel although it has become so complicated that it seems one needs a degree to use all its features. Thanks for sharing.

    • Mel Carriere profile image

      Mel Carriere 2 years ago from San Diego California

      I do a little tinkering with Excel, so this could prove useful. I use the COUNTIF quite a bit, but I haven't dabbled with this one. Great hub.

    • ercramer36 profile image
      Author

      Eric Cramer 3 years ago from Chicagoland

      Thanks Nancy for your comments!

    • Nancy Owens profile image

      Nancy Owens 3 years ago from USA

      This is a very nice Hub. You explain both the how and the why components of using this function. I took the Word specialist exam and am now a certified specialist in Word. Becoming certified in Excel is on my to-do list as well. Thank you for writing such a useful Hub.