How to Use the SUMIF Function in Excel

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: How to Use the AVERAGEIF Function in Excel

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:02 SUMIF
  • 0:30 SUMIF in a Formula
  • 1:34 Values Greater or Less Than
  • 2:45 Where the Criteria Is Text
  • 4:31 More Example Formulas
  • 5:50 Lesson Summary
Save Save Save

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Log in or Sign up

Timeline
Autoplay
Autoplay
Speed Speed Audio mode
Lesson Transcript
Instructor: Karen Sorensen

Karen has a Bachelors in Communications. She has 25 years of experience in Information Systems, Adult Learning and Virtual Training.

When you need to sum numbers that meet a specific criteria in Excel, the SUMIF function is the ticket. This lesson will help you to understand the SUMIF function and how to use it in a formula.

SUMIF

In Excel, the SUM function is very common when writing formulas. It allows you to add up the values in a range of cells and provide the total. But, what if you want to add up only the values that meet a specific criteria? The SUMIF function is what you need. This lesson will introduce you to the SUMIF function. We will look at some examples and learn how to use it in formulas.

SUMIF in a Formula

The first thing you need to know is the syntax for using SUMIF in a formula. The syntax, or the structure of the formula, is =SUMIF(range,criteria,sum_range). Here is an example: =SUMIF(A3:A10,A1,B3:B10).

One way to use the SUMIF in a formula is to add up values that are greater than or less than a specific number. For instance, you could add up the numbers in a range, where the number is greater than 500. Or, you could add up only the numbers in a range that are less than 1,000.

The second popular way to use SUMIF is to add up values in a range, where the criteria is equal to a text value. For instance, adding only the sales numbers for Susan. Let's take a closer look at these two ways to use the SUMIF function in a formula.

Adding Values That Are Greater or Less Than

So, one way to use the SUMIF function is to total numbers that are greater or less than a specific value. Let's use an example. Imagine you have a worksheet of sales results. In column A you have the account name that was sold, in column B you have the seller name that closed the deal, and column C has the dollar amount sold.

Using the SUMIF function, let's write a formula to total all dollar amounts over 499. In cell C16, enter the following formula: =SUMIF(C2:C14,''>499''). And, there you go. The formula totals only the amounts where the value was more than 499. Notice that the greater than symbol and the amount of 499 has quotation marks on each side. This denotes that we are using operators and actual numbers in the formula, as opposed to just a cell reference.

Adding Values Where the Criteria Is Text

Okay, we looked at using the SUMIF function when looking for and adding up numbers when the value is greater or less than a certain amount. But, let's look at another, very common way to use the SUMIF function. If we go back to our example worksheet of sales results, one option is to total the dollar amounts for each seller. For instance, we could write a formula that tells Excel to look for all entries where Susan was the rep, add only those dollar amounts together, and give us just Susan's total.

In the worksheet, in column E, you have entered the names for the three sellers: Susan (which is in E3), Brad (which is in E4), and Jackie (which is in E5). You would like to put their individual sales results in column F, adjacent to their names. Let's start with Susan: =SUMIF(B2:B14,E3,C2:C14).

Worksheet for example
worksheet for example

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support