Excel 2010-2019
Excel 2007
Excel 2003
Tips & Tricks
Excel Forum
Course Index CIG Excel Book
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
Tips & Tricks

New Tips Added Weekly!
Click here to get on our Mailing List


Q: I've got a spreadsheet of companies, what states they're in, and sales amounts. How can I show just the SUM of sales from all of the companies from a specific state (say NY) only?
A: You can use the SUMIF function to add up only those values in a particular range that you want.

Here's a basic example. First I've got a list of numbers. I want to add up the numbers, but ONLY those numbers that are over 40. Let's say I want to count employee work hours, but only those hours for employees who worked 40 hours or more.



  You can see the SUMIF function takes at least two parameters. The first is the range you're searching in for the "IF" part of the function. The second part is the criteria, which can be a simple inequality like I used here, but it needs to be enclosed in quotation marks. Here I've said, "SUM up the values from B1 to B4, IF the value is greater than or equal to 40." If I press Enter, I get:

  I get 93 which is correct. Only Alice and Bill should have been added.

Here's an example that answers your question. This one adds up all of the sales, but only from companies from NY:


  Press Enter I get 3500 which is correct:

  SUMIF can take a third parameter which is the range you want to add up. If you don't specify it, SUMIF uses the first range. Basically this function adds up all of the values from C1 to C7 where the corresponding entries in B1 to B7 are equal to "NY". See how easy?

Now, there's also a COUNTIF function which is very similar. Instead of adding up the values, however, it just counts them. Try this one:


  In this example I have a list of company names. I want to count all of the names that include the phrase "American" in them. This COUNTIF function says count up all of the entries from A1 to A6 where "American" is found. I included the wildcard characters (the * asterisks) to say that any number of characters can come before and after the search phrase - otherwise it would have looked for just the word American by itself. Press Enter and you'll get 3.

See how easy COUNTIF and SUMIF are?

You can learn about these and many more functions just like them in my Microsoft Excel video tutorials.


By Richard Rost   Richard Rost on LinkedIn Email Richard Rost
Click here to sign up for more FREE tips




The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 7/23/2024 2:12:48 AM. PLT: 0s