ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

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


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.

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.


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
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.
Sum Range
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.

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:


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


    0 of 8192 characters used
    Post Comment
    • Atanas Yonkov profile image

      Atanas Yonkov 

      2 years ago from Bulgaria

      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 Jean Mckay profile image


      4 years 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 

      5 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 imageAUTHOR

      Eric Cramer 

      6 years ago from Chicagoland

      Thanks Nancy for your comments!

    • Nancy Owens profile image

      Nancy Owens 

      6 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.


    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at:

    Show Details
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the or domains, for performance and efficiency reasons. (Privacy Policy)
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)
    ClickscoThis is a data management platform studying reader behavior (Privacy Policy)