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
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:
(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:
(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:
(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:
(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:
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:
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:
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.
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:
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?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
