Excel 2010/2013
Excel 2007
Excel 2003
Tips & Tricks
Excel Forum
Course Index CIG Excel Book
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
Tips & Tricks

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

   
 

Using SUMIF and COUNTIF
 
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.

 

=SUMIF(B1:B4,">=40")

   
  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:

 

=SUMIF(B1:B7,"NY",C1:C7)
   
  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:

 

=COUNTIF(A1:A6,"*American*")
   
  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 Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

Click here to sign up for more FREE tips

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP