Excel SUMIF Function
As the name suggests SUMIF function is a formula formed by combining ‘SUM’ and ‘IF’ condition. It implies that this function is can add cells or items that satisfy particular criteria.
What is SUMIF function in Excel?
The excel worksheet has a built-in function called the ‘SUMIF’ which adds all the numbers in a given range of cells based on a single criterion.
How is SUMIF function used in Excel?
In an excel worksheet, SUMIF function is entered as a part of the formula in a cell along with the specified range and criteria. The method of how to use this function as a formula is detailed in the further section.
Formula or Syntax
Parameters or arguments of SUMIF function
Excel SUMIF function uses the following arguments:
Range(required): The range of cells for which you want to apply criteria.
Criteria(required): It determines which cells are to be added. Note that this parameter is limited to text, number or a range and the function cannot use array constants.
[sum_range] (optional): It is the range of cells which is to be summed. If this parameter is not used then the parameter ‘range’ is taken as the ‘sum_range’.
Now let us try how to apply the formula in an excel worksheet and understand them to step by step.
- Let us take a simple example as shown below. Suppose we have a sales report of three states of the US and our objective is to find the total amount earned from the “Florida” region.
Figure 1. Sales table as an example for SUMIF function
- Let us now try and apply the SUMIF function formula to calculate the total amount. The ‘range’ here is from cell A2 (row 2 and column A) to A7 (row 7 and column A).
Figure 2. SUMIF function showing the assignment of the ‘range’ parameter
- The range is applied as shown in figure 2 and the next parameter to be specified is the ‘criteria’. Since we need to find the total amount earned by “Florida” so, this has to be specified in the argument list as shown.
Figure 3. SUMIF function showing the assignment of the ‘criteria’ parameter
- The next parameter is the sum_range which is optional as specified before. Here are total sum_range is the amount column from which the total amount earned by Florida is calculated. The cell range is from D2 (row 2 and column D) to D11 (row 11 and column D) .
Figure 4. SUMIF function showing the assignment of the ‘sum_range’ parameter
- The ‘SUMIF’ function calculates the total amount earned by Florida as shown below.
Figure 4. SUMIF function successfully calculates the total amount earned by Florida.
- Lets us consider the same table as shown in example 1 but this time let us calculate the total amount earned before 2/9/2018 where 2/9/2018 is the criteria. The formula is ‘=SUMIF(B2:B7,”<2/9/2018”,D2:D7)’, where the ‘<’ indicates the range of dates below 2/9/2018 (criteria).
Figure 5. SUMIF function used to calculate the total amount earned before 2/9/2018
- The output obtained is as shown below which successfully calculates the amount earned before 2/9/2018.
Figure 6. SUMIF function successfully calculates the total amount earned before 2/9/2018
Note on the usage of SUMIF function
- Text criteria, or the criteria that include mathematical symbols, must be enclosed in double quotation marks (“).
- Numeric criteria can be supplied without quotation marks(“).
- The wildcard characters such as ? and * can be used in criteria. A question mark matches any ‘one’ character and an asterisk matches any ‘sequence’ of characters.
- To find a literal, use a tilde (~) in the front question mark or asterisk (i.e. ~?, ~*).
- ‘SUMIF’ function cannot be used with logical operators such as AND, OR etc.,