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
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.
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.
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.
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.
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|
Highlight cells D6:D45 on the "Sales Register" tab and hit F4 to anchor the cell reference.
Click on cell B5 on the "Dashboard" tab.
Highlight cells E6:E45 on the "Sales Register" tab and hit F4 to anchor the cell reference.
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.
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.
Below is a what the actual dashboard would look like:
Excellent Resource for Excel Help
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.
© 2014 Eric Cramer
Atanas Yonkov from Bulgaria on December 17, 2017:
Very nice and informative article. You can also check my article about sumifs and countifs functions. They are extremely useful when dealing with more than one criteria.
Fiona from South Africa on May 30, 2016:
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 from Snowbound and down in Northern Colorado on May 26, 2015:
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.
Eric Cramer (author) from Chicagoland on March 28, 2014:
Thanks Nancy for your comments!
Nancy Owens from USA on March 28, 2014:
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.