From data to report in one minute or less. Ajelix BI is launched! 🚀 Get started for free.

How To Use SUMIF function in Excel with Examples

What is SUMIF function in Excel?

The SUMIF function in Excel lets you add up values in a range based on specific criteria, making it a useful tool for a variety of applications. The syntax for this function =SUMIF(range, criteria,[sum_range])

Description

The SUMIF function in Excel allows you to quickly add a range of values based on a set of criteria. It is a versatile formula that can be used in a variety of ways, from adding up sales figures to calculating a budget.

You can use the formula to add up the sales figures for a specific item in a range of cells that contains multiple items and to add up the total sales for a particular region in a range of cells that contains multiple regions.

The function can calculate a budget just add up the budget figures for a particular expense in a range of cells that contains multiple expenses. Or add up the budget figures for a particular region in a range of cells that contains multiple regions.

Struggling with your Excel formulas?
Looking for a faster and easier way to write Excel formulas? Try AI Excel Formula Generator and turn your text into formulas with just a few clicks.

Try for free

Syntax

=SUMIF(range, criteria, [sum_range])

Where:

  • Range = the range of cells that you want to apply the criteria
  • Criteria = the criteria used to determine which cells to sum
  • Sum_range = the range of cells to add together (optional)
SUMIF function MS Excel - Excel Formula Cheat Sheet

How to use SUMIF function in your workbook:

  1. Open the Excel file containing the data you want to use for the function.
  2. Select the cell you want to display the result of the SUMIF formula.
  3. Enter the SUMIF formula into the cell by typing “=SUMIF(” Select the range of cells you want to check for the criteria.
  4. Type a comma “,” Enter the criteria you want to check in the range of cells.
  5. Type a comma “,” Select the range of cells you want to sum if the criteria is met.
  6. Type a closing parenthesis “)”
  7. Press the “Enter” key on your keyboard to execute the formula.

FAQ

What does SUMIF do?

SUMIF adds the values in a range that meet a specific criterion. It essentially sums only the cells that match a certain condition.

What kind of criteria can it handle?

Similar to COUNTIF, you can use numbers, text, dates, logical values, and comparisons like greater than (>), less than (<).

Can SUMIF handle multiple conditions?

No, SUMIF itself can only handle one condition. For multiple conditions, use the SUMIFS function (introduced in Excel 2016) or creative workarounds with nested SUMIFs and logical operators (AND, OR).

What are some alternatives for complex criteria?

SUMIFS function for handling multiple conditions. SUMPRODUCT function for more advanced criteria.

Read latest blog articles about Excel

WEEKLY BLOG ARTICLES WITH INDUSTRY NEWS AND HELPFUL GUIDES

How To Add Drop Down List in Excel

How To Add Drop Down List in Excel

Continue reading

How To Add Checkboxes In Excel

How To Add Checkboxes In Excel

Continue reading