What is SUMIF function in excel
The SUMIF and SUMIFS functions in Excel are used to sum values in a range that meet one or more criteria. The SUMIF functions can be used to sum values based on a single criterion, while the SUMIFS function can be used to sum values based on multiple criteria.
In the below examples we will use how to use SUMIF and SUMIFS function.
SUMIF function in excel
Excel SUMIF function used when we have to sum a set of data based on single criteria. Below is given the syntax for SUMIF that needs to follow to get the result.
SUMIF(range, criteria, sum_range)
range: The range of cells that contains the values you want to sum.
criteria: The criterion that you want to use to filter the range.
sum_range: The range of cells that contains the values you want to sum.Below we will see sumif in excel example to understand it:
In the above image we can see that we tried to find total sum of quantity against Rakesh.Also we can observe that there are multiple entries are against this name. So we tool C3:C17 as name range and C20 as criteria and D3:D17 as sum_range.
SUMIFS function in excel
When you handle complex needed sets of data, SUMIFS functions, which apply when more than one condition set must be satisfied. The SUMIFS function uses a similar syntax, but it takes multiple criteria and criteria ranges as arguments:
SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
sum_range: The range of cells that contains the values you want to sum.
criteria_range1: The range of cells that contains the first criterion.
criteria1: The first criterion.
criteria_range2: The range of cells that contains the second criterion.
criteria2: The second criterion.
You can add additional criteria ranges and criteria to the function as needed.Excel sumifs example is illustrated below:
In the above example we can see that we tried to find Total quantity for Rakesh for the date 02-01-2023. As there are multiple criteria to sum so we will use SUMIFS. There is one point to to be remember here that we will first use sum_range in sumifs formula in excel.
Using wildcards in SUMIF and SUMIFS
You can use wildcards in the criteria arguments of SUMIF and SUMIFS to match multiple values. The following wildcards are available:
- Asterisk (*): Matches any sequence of characters.
- Question mark (?): Matches any single character.
For example, the following formula will sum the Total of all that start with the letter “R”:
=SUMIF(C3:C17,”R*”,D3:D17)
The following formula will sum the sales of all products that end with the letter “an”:
=SUMIF(C3:C17,”*an”,D3:D17)
Conclusion
The SUMIF and SUMIFS functions are powerful tools that can be used to sum values in a range based on one or more criteria. These functions can be used to solve a variety of problems, such as calculating sales by product, region, or time period.
You can read more on SUMIF function for this link.
You can download the excel file from below.