What is the SUMIF function?
The SUMIF function is a built-in function in Microsoft Excel that is a variation of the SUM function. It can sum specified cells based on specified criteria.
What does the SUMIF function do?
The function of the SUMIF function is to sum the specified cells according to the condition. For example, you can use the SUMIF function to calculate the total sales of a product or to calculate the total revenue for a region.
SUMIF parameter details
The basic syntax of the SUMIF function is:
=SUMIF(range,criteria,[sum_range])where the range parameter is the range of cells to check; the criteria parameter is the criteria to apply; and the sum_range parameter is the range of cells to sum (optional parameter).
The following is a detailed description of each parameter:
- range: The range of cells to check, which can be a single cell, a range of cells, or a named range.
- criteria: The criteria to apply, which can be a number, text, logical value, or expression.
- sum_range: The range of cells to be summed, which can be a single cell, a range of cells, or a named range. If this parameter is omitted, the SUMIF function will sum the eligible cells in the range parameter.
How to use the SUMIF function
Example 1: Calculating total revenue for a region
Suppose you have the following table of sales data:
| Regions | Products | Sales |
|---|---|---|
| Beijing | A | 1000 |
| Beijing | B | 2000 |
| Shanghai | A | 1500 |
| Shanghai | B | 2500 |
If you want to calculate the total income for the Beijing region, you can use the following SUMIF function:
=SUMIF(A2:A5,"Beijing",C2:C5)What this function means is to check whether the value in the cell range A2 to A5 is equal to “Beijing”, and if so, sum the values in the corresponding position in the cell range C2 to C5, and the result is 3000.
Example 2: Calculate the total sales of a product
If you want to calculate the total sales of product A, you can use the following SUMIF function:
=SUMIF(B2:B5,"A",C2:C5)What this function means is, check if the value in the cell range B2 to B5 is equal to “A”, and if so, sum the values in the corresponding position in the cell range C2 to C5, and the result is 2500.
Example 3: Calculating total revenue with sales greater than 1000
If you want to calculate the total revenue for sales greater than 1000, you can use the following SUMIF function:
=SUMIF(C2:C5,">1000")What this function means is, check if the value in the cell range C2 to C5 is greater than 1000, and if so, sum the values at the corresponding positions, and the result is 6000.
Summarize
The above is the tutorial shared by the editor on how to use the SUMIF function, which can sum the specified cells according to the specified conditions. Especially when you need to perform data analysis or make reports in Excel, it is very useful.
Recommended: Excel Series Articles
Excel Function Tutorial (3): How To Use The HLOOKUP Function