SUMIF Function

The SUMIF function in Excel is a powerful tool for conditional summing. Whether analyzing sales data, tracking expenses, or calculating metrics, SUMIF helps by summing values that meet specific criteria. It’s perfect for tasks like calculating one product’s total sales or summing expenses from a certain category.

Syntax of SUMIF Function in Excel

=SUMIF(range, criteria, [sum_range])

Argument	Description
range (required)	The range of cells you want to evaluate.
criteria (required)	The condition or criteria that determine which cells to include.
sum_range (optional)	The range of cells to sum. If omitted, Excel sums the cells in the range parameter.

Examples of SUMIF Function

SUM Values Based on a Condition

Add the sales data only from the North region using SUMIF.

• Formula: =SUMIF(A2:A5,"North",B2:B5)
• Description: The criteria “=North” is verified with the range A2:A5 and its proportional values in the sum_range B2:B5 are added. Since there are two entries in the range A2:A5 matching “=North”, their proportional values (100 and 150) are added using SUMIF.
• Output: 250

SUM Values Based on Logical Operators

SUM sales value of months where sales are more than 8000$.

• Formula: =SUMIF(B2:B6,">8000")
• Description: The SUMIF function compares the range B2:B5 against the criteria “>8000$” and adds values that matches the criteria.
• Output: 36000$

Using SUMIF with Other Functions in Excel

Using SUMIF with DATE Function

SUM sales made in a specific month using SUMIF.

• Formula: =SUMIF(A2:A5, ">=01/01/2024", B2:B5) - SUMIF(A2:A5, ">=01/02/2024", B2:B5)
• Description: First SUMIF sums all sales from January 1st onward. The Second SUMIF adds sales from February 1st onward. Both values are subtracted resulting in the sales value of January only.
• Output: 1200 (500 + 700)

Using SUMIF with LEFT Function

Sum values based on the first character in a product code using SUMIF.

• Formula: =SUMIF(A2:A5, LEFT("A123", 1) & "*", B2:B5)
• Description: The LEFT function in the criteria picks the first character from the text string A123 i.e A and concat with wildcard *. Further, the SUMIF considers the criteria “=A*” for the range A2:A5, i.e all products with codes starting with A and adds proportional values in the sum_range B2:B5.
• Output: 1400

Related Functions of SUMIF

For more advanced functionality, consider using the SUMIFS function as it allows for multiple criteria.

• Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
  • sum_range: The range of cells that you want to add together. This is the range containing the values to be summed.
  • criteria_range1: The first range that Excel evaluates based on the given condition.
  • criteria1: The condition or criterion for criteria_range1. This could be a number, expression, cell reference, or text that defines which cells to consider.
  • [criteria_range2, criteria2], …: (Optional) Additional ranges and conditions. You can specify multiple criteria ranges and corresponding criteria, allowing you to sum cells that meet multiple conditions.

Common Errors While Using CONCAT

VALUE! Error

Occurs if the criteria contain text and aren’t enclosed in quotes. Ensure text-based criteria are in quotes, e.g., =SUMIF(A2:A20, "Apples", B2:B20).

Incorrect Results

This can happen if range and sum_range have different sizes. Always ensure range and sum_range are the same size for accurate results.

SUMIF Function – Tips and Best Practices

• Use Wildcards: Use * for any number of characters and ? for a single character. For example, =SUMIF(A2:A20, "Electro*", B2:B20) sums all rows where the cell starts with “Electro”.
• Combine with Other Functions: Pair SUMIF with IFERROR to handle errors, e.g., =IFERROR(SUMIF(A2:A20, "Electronics", B2:B20), 0).
• Date Criteria: To sum data based on dates, use criteria like ">=" & DATE(2024,1,1).

Conclusion

The SUMIF function is a powerful Excel tool for summing data based on specific conditions. With its straightforward syntax and flexibility, mastering this function can significantly enhance your data analysis skills. Start using SUMIF today to make your Excel workflow more efficient!

Frequently Asked Questions (FAQs)