The Excel SUM function is one of the most basic functions. Although it is known for calculating the total of a specified range, depending on how it is used, it can also calculate totals with multiple criteria as an alternative to SUMIFS.
Excel SUM Function Basics and Uses
The SUM function, Excel’s primary addition function, can be categorized into four main formula syntaxes.
The most basic Excel addition formula calculates a total by specifying a range.
G3 cell =SUM(E3:E13)
Next is a formula syntax that calculates the sum of selected individual cells.
G7 cell =SUM(E4,E7,E10)
When text appears between numbers, as in column D below, the following formula syntax was used to calculate the total of only the numbers.
G9 cell =SUM(D3,D4,D6,D7,D9,D10,D12,D13)
However, the correct way to add only numbers with the Excel SUM function is to use the formula below rather than the method above.
G13 cell =SUM(D3:D13)
The SUM function automatically distinguishes text from numbers and adds only the numbers.
How to Use Multiple Criteria with the Excel SUM Function
The SUM function’s multiple-criteria capability can be used as an alternative to the COUNTIF function.
First, the formula syntax is as follows.
G6 cell =SUM((D3:D13>G3)*(C3:C13=G4)*(E3:E13>G5))
G6 cell =SUM((PRICE>100)*(PRODUCTS=BOTTOMS)*(Sales quantity>1000))
There are two cells that meet all the multiple criteria: a price greater than 100, a product of BOTTOMS, and a sales quantity greater than 1,000.
Another multiple-criteria capability of the Excel SUM function is that it can use range conditions.
The formula syntax is as follows.
G6 cell =SUM((D3:D13>H3)*(C3:C13=H4)*(F3:F13>E3:E13))
G6 cell =SUM((D3:D13>H3)*(C3:C13=H4)*(SALE1>SALE2))
There is one cell that meets all the multiple criteria: a price greater than 100, a product of BOTTOMS, and SALES 1 exceeding SALES 2 in sales quantity.
Conclusion
Using multiple criteria and range conditions is one of the greatest advantages of the Excel SUM function.
First, using multiple criteria lets you calculate the sum of data that meets several conditions at the same time. For example, to calculate the total of products sold in a specific region during a specific period, you must select only the data that meets both the region and period criteria. With the SUM function, you can easily specify multiple criteria and quickly calculate the total of the selected data.
Second, using range conditions lets you select only data within a specified range and calculate its total. For example, to calculate the total of products sold during a specific period, you must select only the data within that period. With the SUM function, you can easily specify these range conditions and quickly calculate the total of the selected data.
Therefore, using multiple criteria and range conditions is very useful for data analysis and report creation. These capabilities help you quickly and accurately extract the information you need from complex datasets.