How to use SUMIF with 2 or more conditions in Excel
You already know the SUMIF function to calculate conditional sum in Excel, but you do not know how to use the SUMIF function with 2 or more conditions in Excel. So you let TipsMake.com find out in this article.
Here TipsMake.com share to you how to use the SUMIFS function with 2 or more conditions in Excel, please follow along.
The SUMIF function is a function that sums 1 condition.If you want to sum with 2 or more conditions in Excel, you need to use the SUMIFS function.
Syntax of SUMIFS function:
= SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], .)
Inside:
- Sum_range : a required argument, this is the range of cells to sum.
- Criteria_range1 : is a required argument, this is the range to be tested using Criteria1 .
- Criteria1 : required argument, this is the condition that identifies a cell within Criteria_range1 .
Criteria_range1 and Criteria1 are set as a search pair, whereby a range is searched according to specific criteria.
- Criteria_range2, Criteria2 . are optional arguments, these are additional ranges and conditions associated with the argument. The SUMIFS function supports up to 127 range of conditions / conditions.
For example , using the SUMIFS function with 2 or more conditions in Excel.
Suppose you have the data table as shown below:
Because of the total sale proceeds, Sum_range is the total sum column D7: D15 .
According to this requirement, the SUMIFS function will have 2 pairs of range / condition:
- Scope 1 is A7: A15 with condition 1 is "iPhone 6s plus"
- Scope 2 is F7: F15, provided that condition 2 is "Thu Hà"
Thus, the SUMIFS function will look like this:
= SUMIFS (D7: D15, A7: A15, "iPhone 6s plus", F7: F15, "Thu Ha")
Because of the total sale, Sum_range is D7: D15
This requirement has 3 conditions with the following range / condition pairs:
- Range 1 is A7: A15, provided that the "BlueTuoch LP-V6 Speaker"
- Range 2 is E7: E15 provided that "June 10, 2019"
- Range 3 is F7: F15 provided that "Thu Nguyet"
Thus the SUMIFS function syntax is as follows:
= SUMIFS (D7: D15, A7: A15, A10 , E7: E15, E10 , F7: F15, F11 )
Above TipsMake.com showed you how to use the SUMIFS function with 2 or more conditions in Excel. Hopefully through this article, you will better understand the SUMIFS function in Excel to use summing when two or more conditions are required. Good luck!
You should read it
- Sumif and Sumifs functions in Excel
- How to calculate the total value based on multiple conditions in Excel
- SUMIF and SUMIFS functions - specific usage and examples
- The way to sum the same codes in Excel
- Differentiate between SUM, SUMIF, SUMIFS and DSUM functions
- The SUM function (sums) in Excel
- How to format data based on other cell conditions in Excel
- The SUMIFS function, how to use multiple conditional calculation functions in Excel
May be interested
- The SUMIFS function, how to use multiple conditional calculation functions in Excelthe sumifs function in excel calculates multiple conditions, combining multiple conditions.
- How to automatically create valuable cell borders in Excelthere are many ways of highlighting valuable cells in excel, such as creating borders for cells automatically when entering values.
- How to list conditional lists in Excelhow to list conditional lists in excel. the lookup function is available in excel so that users can search by conditions but return only the first true value. if you need to list all the conditions and copy it to a different data sheet or another sheet ...
- The search function satisfies many conditions in Excel - Usage and examplesthe search function satisfies many conditions in excel - usage and examples. in the process of processing data in excel many times you need to search for data that satisfies many conditions to extract data, so you are looking for a function that satisfies many things.
- Instructions on how to use the Dmax function in Excelthe dmax function in excel is used to find the maximum value in the data table with conditional depending on the conditions that the user uses.
- Steps to create relationships between multiple tables using Data Model in Excelexcel is a powerful tool for data analysis and automation after processing large data sets. you can spend considerable time analyzing tons of data using vlookup, index-match, sumif...
- How to use Hlookup function on Excelhlookup function basically also has the function syntax and features like vlookup function, which is to help users find data in excel table, with the conditions or given information. here is the guide for using the hlookup function in detail.
- How to use the IF function in Excelthe if function in excel helps you easily check conditions and return corresponding results, providing optimal support in calculating and processing data. this is one of the important functions, widely used to automate the comparison and classification of information.
- Instructions on how to extract data in Excelinstructions on how to extract data in excel. you want to extract data according to the conditions for printing, but the data areas with the same conditions are far apart, you have to gather them together before you can print. in this article help you extract data
- How to use AVERAGEIF function in Excelaverageif function on excel is a conditional plus average function in the data table, with the given conditions set by the user.