How to Use the SUMIF Formula in Excel

The SUMIF formula is a function in Excel used to sum the values in a range that meet one criterion that you specify. This is different from SUMIFS because SUMIFS can handle multiple criteria int the formula.

SUMIF Formula Explanation

     =SUMIF(range, criteria, sum_range)

The following are the 3 required syntax of the formula:

Range (required): The range of cells that you want to be evaluated by your criteria. The cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored. The selected range may contain dates in standard Excel format.

Criteria (required): The criteria are the item you would like to lookup in the range. It can be a number, expression, a cell reference, text, or a function that defines which cells will be added together in your SUMIF formula.

Sum_Range (optional): The actual cells to add if you want to add cells other than those specified in the range argument. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria are applied).

Example Using the SUMIF Formula:

The General Manager of a second-hand car dealership wants to know how much sales revenue was earned within California for the current year to date.

The Financial Analyst is asked to find out. The Financial Analyst generates a report from the Sales Journal (below) which contains raw sales data by date of sale and location. The Financial Analyst uses the SUMIF formula to determine the sales figure.

Here,

i. Range formula was used for referencing the branch location column.

ii. Criteria formula was used for qualifying a certain branch location (e.g. California).

iii. Sum_range formula was used for calculating the sum of the Company’s sales revenue, by selecting the entire range of sales revenue column.

As shown from the data table above, the SUMIF formula can be used to determine data with certain criteria, in this case the branch location of just California, by simply highlighting the data column range of sales revenue.

I hope that helps. Please leave a comment below with any questions or suggestions. For more in-depth Excel training, checkout our Ultimate Excel Training Course here. Thank you!