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!
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(.
Step #2 – Drag to select the range B4:B7.
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.
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.
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.
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″)
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
Final Thoughts
The SUM function, along with SUMIF and SUMIFS, are useful for calculating totals in various Excel worksheets.
If you’ve enjoyed reading this article, you might want to check out XLOOKUP vs VLOOKUP: Excel Trainer Explains Key Differences. These functions are useful for anyone who needs to combine data from multiple sources.
See you next time!
