Categories
Microsoft Excel

Excel’s SUM Function vs SUMIF, SUMIFS

One of the most common calculations in Excel involves adding numbers. The SUM function in Excel makes it easy and convenient to add values in rows or columns. People do it so frequently, Microsoft has created a button specifically for this purpose – AutoSum.

The AutoSum button lets you quickly add rows or columns of data. It can be found in two locations on Excel’s Ribbon:

  • Home > Editing
  • Formulas > Function Library

In this article, we’re going to take a look at 3 functions: SUM, SUMIF, and SUMIFS. Each function is used to calculate a total for a series of numbers, but they accomplish the task in different ways.

Let’s get started!

Excel Courses: We offer Microsoft Excel training delivered online by a live instructor. Participants can connect from anywhere in Canada.

How Do I Use the SUM Function?

The SUM function adds a series of values. Values can include numbers, cell references, ranges, or a mix of all three. The SUM function can accommodate up to 255 arguments.

Syntax

=SUM(number1, [number2], [number3], …)

number1 is the first value to sum.

number2 [optional] is the second value to sum.

number3 [optional] is the third value to sum.

Example

There are two ways to use the SUM function. You can type it, like any other formula, or you can click AutoSum. We’ll look at both options.

Typing the SUM Function

The first way to enter the SUM function involves typing it into your worksheet.

Step #1 – Select cell B8 and type =SUM(.

Typing the SUM function
Typing the SUM Function

Step #2 – Drag to select the range B4:B7.

Using the SUM function to add values in B4:B7
SUM Function with Range Selected

Step #3 – Type ) and press Enter.

Using AutoSum

The second method of entering the SUM function involves using the AutoSum button. This method is easier, especially if you need to calculate a sum for multiple rows or columns.

Here’s an example:

Step #1 – Select cell C8 and click the AutoSum button in the Home > Editing group.

Another SUM function
Invoking the SUM Function with AutoSum

Step #2 – Click AutoSum again or press Enter.

If you need to calculate a total for multiple columns (or rows), just select several cells and click AutoSum. No need to press Enter afterward.

In this example, I selected the range D8:E8 and clicked AutoSum. Totals were calculated for both selected cells, using values from the columns above. Then I selected F4:F8 and clicked AutoSum again to calculate totals for all 5 rows.

Calculating totals for multiple rows
Calculating Totals for Multiple Rows

How Do I Use the SUMIF Function?

The SUMIF function adds numbers in a range that meet criteria you specify.

Syntax

=SUMIF(range, criteria, [sum_range])

range is the range of cells you’ll evaluate.

criteria are the conditions you’ll use to determine which rows to include in your calculation.

sum_range [optional] is the range of cells you’ll use to calculate a total. If you omit this argument, values in range will be used to calculate a total.

Example

In this example, I entered the formula =SUMIF(B4:B15, “Baked Goods”, C4:C15) in cell C17. Note that because my criteria contained text, it had to be enclosed in quotes.

Using the SUMIF function
The SUMIF Function

The SUMIF function parses through the range B4:B15, looking for “Baked Goods”. The first instance is in cell B4. The SUMIF function starts a running total with the value in C4.

The next instance of baked goods is in cell B8. SUMIF adds 22 to the running total… this process continues until the entire range has been parsed and all instances of “Baked Goods” have been located. Total sales for Baked Good is $54.

How Do I Use the SUMIFS Function?

The SUMIFS function adds numbers in a range that meet single or multiple criteria.

Syntax

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2], …)

sum_range is the range of cells you’d like to add.

criteria_range1 is the first range of cells you’ll evaluate.

criteria1 is the first condition(s) you’ll use to determine which rows to include in your calculation.

criteria_range2 [optional] is the second range of cells you’ll evaluate.

criteria2 [optional] is the second condition(s) you’ll use to determine which rows to include in your calculation.

Example

In this example, I clicked in cell C17 and typed the following formula:

=SUMIFS(C4:C15,B4:B15,”Baked Goods”,A4:A15,”<=4/2/20″)

Using the SUMIFS function
The SUMIFS Function

Let’s go over this application of the SUMIFS function.

C4:C15 is the sum_range.

B4:B15 is the first criteria range.

A4:A15 is the second criteria range.

This calculation will include numbers from corresponding rows in C4:C15, if an entry in B4:B15 includes “Baked Goods” and the date in A4:A15 is on or before April 2, 2020.

These evaluations are carried out on a row-by-row basis. Note that both conditions need to be true before a value from column C is added to the running total.

In row 4, the transaction date was April 1 and the category was Baked Goods so 18 is used to start the running total.

In row 5, the transaction date was April 1 but the category was NOT baked goods so 57 is NOT added to the running total.

Row 4 and row 8 were the only records that satisfied both conditions. Values from column C were included in the running total.

18 + 22 = 40

Using the SUMIFS function with multiple criteria
SUMIFS Function with Multiple Criteria

Microsoft Excel is a registered trademark of Microsoft Corporation. XL Basics contains articles and educational content related to Microsoft Excel but is not affiliated with Microsoft in any way.

By Michael Belfry

Working as a full-time training consultant, Michael provides Microsoft Office courses to government and private sector clients across Canada.