SUMIF Function in Excel

What is the SUMIF Function?

The SUMIF function in Excel is a powerful tool that allows users to efficiently calculate the sum of cells based on specified criteria. The SUMIF function adds the cells specified by a given condition or criteria. Whether you’re working with a single criterion or multiple criteria using the SUMIFS function, mastering these formulas can significantly enhance your data analysis capabilities.

How to Use the SUMIF Function

Most analysts will be familiar with the SUM Excel function which can be used to add up data in the range. The SUMIF function is used to calculate the sum of cells within a range that meet specified criteria. The formula contains three arguments: the range, the criteria, and the sum range.

Here is an example of how to use the SUMIF function. We have been asked to find out how much money has been made from transactions of over 40 units. This can be solved using the SUMIF function.

1. Type “=SUMIF(“ in the cell where you want the result to appear
2. Select the range of cells you want to evaluate based on the criteria such as “C8:C19”
3. Next, type a comma “,” to move to the next argument
4. Enter the criteria, which is the condition or rule used to determine which cells to include in the sum (E.g. “Yes” if you want the criteria to only select cells which are linked to Yes in column C)
5. Type another comma “,” to move to the next argument
6. Select the sum range, which is the column of cells to sum if the corresponding cells in the range meet the criteria (e.g. “B8:B19)
7. Once all completed, remember to close the bracket “)” and press ENTER to complete the formula and display the result
8. The answer is 1,738.5

SUMIFS Formula

The formula is similar to SUMIF but includes additional ranges and criteria.

Examples of the SUMIF Function in Excel

In this example, we are asked what Betsy’s total sales are. The formula allows us to highlight the data range and then specify to sum the figures if they belong to Betsy.

This type of formula is useful as it will continue to calculate even if the underlying data changes. For instance, if Betsy’s sales had been wrongly attributed to Ashley, the data can be updated and the SUMIF calculation will automatically refresh to include the new data.

SUMIFS Function in Excel

SUMIF is used for a single criterion while SUMIFS is used for multiple criteria.

SUMIFS is a function that calculates the sum of cells within a range that meets multiple specified criteria. It has multiple arguments: the sum range, and pairs of criteria ranges and criteria.

The sum range is the column of cells to sum if the corresponding cells in the range meet the criteria, and the criteria ranges and criteria are the columns of cells you want to evaluate based on the criteria and the conditions or rules used to determine which cells to include in the sum.

Examples of the SUMIFS Function in Excel

In this example, we are asked how many units of lip gloss Betsy has sold. The SUMIFS function can solve this as it will filter the units sold (column F) firstly by all the sales in Betsy’s name (column C) and then cross-referencing all Betsy’s sales to only include lip gloss sales (denoted in column E).

Analysts need to be careful when setting up the formula to make sure the ranges are in the correct order, and that commas are used appropriately.

We can see in the formula shown below that the units are selected first (the column to be summed), then Betsy (IF criteria 1), then lip gloss (IF criteria 2).

Conclusion

By understanding how to use the SUMIF function, analysts can streamline workflow, make more informed decisions, and ultimately improve productivity. Take the time to practice and explore the various applications of SUMIF and SUMIFS in Excel projects, and you’ll soon see the benefits of these versatile functions.

Once mastered SUMIF can be combined with other Excel functions to create more creative and advanced Excel formulas. Often, there can be more than one way to draw data from a range so if you are familiar with these functions, data analysis can become fast and efficient.

Additional Resources

30 Key Excel Functions for Finance

Choose Function Formula Scenario Analysis

Match Function