ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to use the SUMIF and SUMIFS functions in formulas in Excel 2007 and Excel 2010 with examples

Updated on April 10, 2013

Introduction

Hi and welcome to my latest hub on Excel. Today, I am going to look at how to use the SUMIF and SUMIFS functions in formulas in Excel.

  • The SUMIF function allows you to sum (or add up) data based on a single criteria. In the example I will be using today, I want to sum all of sales of the different media types in my music shop regardless of artist (so I want total CD, LP and Cassette sales).

In other words using my example, Excel will SUM the contents of the cell IF the media type equals CD.

  • The SUMIFS function allows you to sum data based on any number of criteria you wish. You can use up to 127 different criteria (that would be one heck of a long formula) if you so desire. Today, by way of an example, I will be using SUMIFS to add up the number of CDs, DVDs and LPs by each artist that I have sold.

The advantage of using SUMIF and also SUMIFS is that they are very easy to use. In addition, using the example of my music shop, I am constantly selling (hopefully!) new music; I can easily update my SUMIF and SUMIFS formulas by simply increasing the ranges the formula is adding up as my sales increase. In the figure below, you can see that my SUMIF formula has capacity for another 182 sales.

An example of the SUMIF function used in a formula in Excel 2007 and Excel 2010.
An example of the SUMIF function used in a formula in Excel 2007 and Excel 2010. | Source

The IF function in Excel 2007 and Excel 2010 is very powerful and is made even more powerful when it is combined with other functions as is the case with SUMIF. I have a number of hubs covering different usages of the IF function including:

Using the COUNTIF and COUNTIFS function in formulas to count the number of times a particular item occurs in a list (I counted the number of pigs (COUNTIF) and the number of white pigs (COUNTIFS) I had on my farm by counting the number of times pig or white pig occurred on my list of the animals I have). To learn more about COUNTIF or COUNTIFS, click here:

http://robbiecwilson.hubpages.com/hub/Using-the-COUNTIF-and-COUNTIFS-formulas-and-the-Removing-Duplicates-button-in-Excel-2007

The IF function when combined with the logical functions AND, OR and NOT allows you to search for instances where two things happen together, when one or another occur (but not both) and when something doesn’t happen. This allows you to create powerful functions. I could search for CDs sales by Pink Floyd and AC/DC, or, Pink Floyd or AC/DC or, all CD sales not by AC/DC using these functions. This hub can be found here:

http://robbiecwilson.hubpages.com/hub/Using-the-IF-and-IFERROR-functions-as-well-as-the-logical-functions-AND-OR-and-NOT-in-Excel-2007-and-Excel-2010

Finally, I have used the IF function to convert the result of a formula into something I can use more easily. I used IF statements in my mortgage calculator to convert the item selected by the user of my spreadsheet into a number my formulas could use. That hub can also be found here:

http://robbiecwilson.hubpages.com/hub/Using-the-PMT-PPMT-IMPT-and-ABS-functions-to-create-a-Mortgage-Calculator-in-Excel-2007-and-Excel-2010

Using the SUMIF function in formulas in Excel 2007 and Excel 2010

My music shop is selling albums on CD, LP and Cassette Tape. I would love to easily and quickly know how sales are doing on the different media types. All my sales are in recorded in Excel and I can use the SUMIF function in a formula to find out for example, how may CDs I have sold to date. You can see my list of sales below:

List of sales to be used with the SUMIF function in Excel 2007 and Excel 2010.
List of sales to be used with the SUMIF function in Excel 2007 and Excel 2010. | Source

Now using SUMIF, I will calculate CDs sales across all the artists I stock. The SUMIF function consists of three parts:

  1. The Criteria Range, which is the cells that contain whatever it is you want to sum (in my case CDs) which is C2 – C12
  2. The Criteria, what it is you are going to sum (in this case “CD”)
  3. The Sum Range, which is the cells that contain the cells you wish to sum (in my case sales quantities) which is cells D2 – D12

So to show this using SUMIF in a formula:

=SUMIF(C2:C12,"CD",D2:D12)

This is illustrated in the figure below, but in English I am looking in cells C2 – C12 and D2 – D12 and adding up all the rows in column D which contain CD in column C.

Example of a SUMIF function used in a formula in Excel 2007 and Excel 2010.
Example of a SUMIF function used in a formula in Excel 2007 and Excel 2010. | Source

To add up all the sales of LPs and Cassette Tapes, I simply change the criteria (the part in the quotation marks ““) and leave the two ranges the same.

=SUMIF(C2:C12,"LP",D2:D12)

=SUMIF(C2:C12,"Cassette",D2:D12)

Using the SUMIFS function in formulas in Excel 2007 and Excel 2010

The SUMIF formula is perfect if you only want to add up something based on a single criteria (in my example above, I wanted to add up all CD sales). But suppose I want to add up all sales of CDs by Pink Floyd; or if I wanted to add up all sales of the Pink Floyd album Animals on CD? I wouldn’t be able to use SUMIF as I have more than one criterion. In these cases I would have to use SUMIFS.

The syntax of the SUMIFS function is similar to SUMIF, with one important change.

Note: With SUMIFS, the Sum Range comes first rather than last. This is to avoid confusion with the multiple criteria that you (can) use.

After the Sum Range, next comes the Criteria Range and then the Criteria exactly as we did above with SUMIF.

Simply add any more additional criteria that you need to complete the formula. Now for an example:

I want to sum or add up, all the sales of Pink Floyd albums on CD. To do this:

  • D2:D12 is my Sum range
  • A2:A12 is my first criteria range
  • “Pink Floyd” is my first criteria
  • C2:C12 is my second criteria range
  • “CD” is my second criteria

The formula becomes;

=SUMIFS(D2:D12,A2:A12,"Pink Floyd",C2:C12,"CD")

Example of a SUMIFS function used in a formula in Excel 2007 and Excel 2010.
Example of a SUMIFS function used in a formula in Excel 2007 and Excel 2010. | Source

If I want to add up all the Bruce Springsteen Cassettes I have sold, the formula is:

=SUMIFS(D2:D14,A2:A14,"Bruce Springsteen",C2:C14,"Cassette")

If I want to add up all the sales on CD of the Pink Floyd album Pulse, the formula would be:

=SUMIFS(D2:D12,A2:A12,"Pink Floyd",C2:C12,"CD",B2:B12,"Pulse")

A further example of a SUMIFS function used in a formula in Excel 2007 and Excel 2010
A further example of a SUMIFS function used in a formula in Excel 2007 and Excel 2010 | Source

Note: For both SUMIF and SUMIFS, you can use wild cards in the criteria. The ? can be used to represent one character and the * any number of cells. To use an example; Cassett? and Cass* could both be used to represent Cassette in my example above and would also still provide the correct results.

Note: If you actually want to use a ? or a * as an actual character and not a wild card, put a tilde ~ in front of it, to advise Excel to treat it as a character. So for example, if I wanted to use Who? in a SUMIF formula, I would use:

=SUMIF(C2:C12,"Who~?",D2:D12)

Conclusion

The IF function in Excel 2007 and Excel 2010 is a very powerful and versatile function which can be used in formulas in Excel in a wide variety of ways. In today’s hub, I looked at the SUMIF and SUMIFS functions and how you can use them in formulas.

SUMIF sums (or adds up) data based on a single criteria. In my example, I added up all the sales of CDs in my music store.

SUMIFS sums data based on more than one criterion. So I added up all the sales of Pink Floyd on CD, as well as the sales of specific albums on CD.

Finally, we went through how to use wild cards correctly in SUMIF and SUMIFS.

Many thanks for reading; I hope that you found this hub both informative and useful. Please feel free to leave any comments you may have below.

Comments

    0 of 8192 characters used
    Post Comment

    • profile imageAUTHOR

      Robbie C Wilson 

      3 years ago

      Hi TTGReviews, thanks so much for your kind comment, I am so glad that you found my hub useful and informative.

    • TTGReviews profile image

      TTGReviews 

      3 years ago

      I knew about SUMIF, but not about SUMIFS. This will be very helpful to know in the future.

    • profile imageAUTHOR

      Robbie C Wilson 

      4 years ago

      Hi Daniel,

      Thanks so much for your kind comments. Excel is such a massive and complicated application, I love to learn new things and then write about them. I am so glad that you found my article useful.

    • Daniel1137 profile image

      Daniel Wyvern 

      4 years ago from Athens, Georgia

      SUMIF is very powerful and I've used it many times over the years. I am always surprised at the number of people who were not even aware of it. But I have to admit that I was not aware of SUMIFS. Thanks for the knowledge.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com 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: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    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 googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    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)
    Marketing
    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.
    Statistics
    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)