Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

 How to sumif with one or more criteria in Excel?

In Excel, sum values based on one or more criteria is a common task for most of us, the SUMIF function can help us to quickly sum the values based on one condition and the SUMIFS function help us to sum values with multiple criteria. This article, I will describe how to sum with one or more criteria in Excel?

Sum cell values with one criterion in Excel

Sum cell values with multiple criteria in Excel


arrow blue right bubble Sum cell values with one criterion in Excel


First, I will give the SUMIF syntax to you for reference:

SUMIF(range, criteria, [sum_range])

  • Range: the range of cells to be evaluated by your criteria;
  • Criteria: the condition that you want to use;
  • Sum_range: the range of cells to sum if the condition is met

For applying this SUMIF function, I will take the following data for example:

doc-sum-with-multiple-criteria-1

(1.) Sum of quantity when the product is “KTE”:

=SUMIF(A2:A12,E2,C2:C12) and press Enter key to get the result, (A2:A12 is the range cells which contains the criteria, E2 is the criterion you want to sum the values based on, C2:C12 refers to the range you want to sum) see screenshot:

doc-sum-with-multiple-criteria-2

(2.) Sum the quantity of all products except “KTE”:

=SUMIF(A2:A12,"<>KTE",C2:C12), then press Enter key to add up all product quantity excluding KTE, (A2:A12 is the range cells which contains the criteria, <>KTE is the criterion which excludes KTE, C2:C12 refers to the range you want to sum), see screenshot:

doc-sum-with-multiple-criteria-3

(3.) Sum the quantity greater than 250:

=SUMIF(C2:C12,">250"), and press Enter key to get the result, (C2:C12 refers to the range you want to sum, >250 is the criterion you need, you also can change it to less than 250 as you need), see screenshot:

doc-sum-with-multiple-criteria-4

(4.) Use SUMIF+SUMIF+… function with multiple OR criteria, sum the quantity of KTE and KTW:

=SUMIF(A2:A12,"KTE",C2:C12) + SUMIF(A2:A12,"KTW",C2:C12)( A2:A12 is the range cells which contains the criteria, KTE and KTW are the criteria you want to sum the values based on, C2:C12 refers to the range you want to sum), and then press Enter key to get the result:

doc-sum-with-multiple-criteria-5


arrow blue right bubble Sum cell values with multiple criteria in Excel

The SUMIFS syntax as follows:

SUMIFS(sum_range, criteria_range1, criteria1,[criteria_range2, criteria2],...)

  • Sum_range: a range of cells to sum;
  • Criteria_range1: the first range which has the criteria;
  • criteria1: the first condition that must be met;
  • criteria_range2, criteria2, ...: these are additional ranges and criteria associated with them.

(1.) Sum the quantity where the product is KTE and name is James:

Please enter this formula: =SUMIFS(C2:C12,A2:A12,"KTE",B2:B12,"James"),(C2:C12 is the range of cells need to sum, A2:A12 , KTE are the first criteria range and criterion, B2:B12, James, are the second criteria range and criterion), and then press Enter key, see screenshot:

doc-sum-with-multiple-criteria-6

Here also has an array formula can help you: =SUM((A2:A12="kte")*(B2:B12="James")*C2:C12), and you need to press Ctrl + Shift + Enter keys together after typing the formula.

(2.) Sum the quantity where the product is KTE and name is not James

Type this formula: =SUMIFS(C2:C12,A2:A12,"KTE",B2:B12,"<>James")(C2:C12 is the range of cells need to sum, A2:A12 , KTE are the first criteria range and criterion, B2:B12, <>James, are the second criteria range and criterion), then press Enter key to get the result, see screenshot:

doc-sum-with-multiple-criteria-7

Another array formula also can finish this operation: =SUM((A2:A12="kte")*(B2:B12<>"James")*C2:C12), but you must press Ctrl + Shift + Enter keys after typing this formula.

(3.) Sum the quantity where the product is KTE and quantity greater than 150

You can use this SUMIFS formula: =SUMIFS(C2:C12,A2:A12,"KTE",C2:C12,">150")(C2:C12 is the range of cells need to sum, A2:A12 , KTE are the first criteria range and criterionj, C2:C12, >150, are the second criteria range and criterion), and then tap the Enter key to return the result.

doc-sum-with-multiple-criteria-8

You can also us the SUM array formula to get the result you want, =SUM((A2:A12="kte")*(C2:C12>150)*C2:C12) please remember to press Ctrl + Shift + Enter keys after typing this formula.

(4.) Sum the quantity which quantity between 100 and 200

To sum the values between two numbers, please use this formula: =SUMIFS(C2:C12,C2:C12,">100",C2:C12,"<200")( C2:C12 is the range of cells need to sum, C2:C12 , >100 are the first criteria range and criteria, C2:C12, <200, are the second criteria range and criteria), and then press Enter key to get the result as follows:

doc-sum-with-multiple-criteria-9

Of course, a SUM array formula can help you as well, =SUM((C2:C12>100)*(C2:C12<200)*C2:C12), remember to press Ctrl + Shift + Enter keys together to get the right result.


Related articles:

How to sum multiple columns based on single criteria in Excel?

How to sum cells with text and numbers in Excel?


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.