Posted October 12, 2023

Guest Author: James Ambrose 

The power of Excel and Google Sheets is simply limited by the user's imagination. But for one to fully unlock their full potential using these tools, it is important to build a strong foundation. As such, today we will be going over one of the most widely used functions: =SUM(); as well as its derivatives: =SUMIF(), and =SUMIFS().

How to use the =SUM() function

The =SUM() function is used to add numbers together. In order to talk about how this function is used however, it is important to mention the data format and structure. A well formatted spreadsheet have your data for a specific period of time (i.e. a year, a month, etc.) stacked in a column. As such, in order to add the items in the column, the =SUM() function will typically be at the bottom of the column to highlight the total.

The sum function can be used in two different ways.

The first way is by selecting the individual items the user would like to add together. In Figure 1, the user is essentially saying that they would like to add Item 1, Item 2, Item 3, Item 4, and Item 5.

The second way is by selecting the group (or array) of data the user would like to add together. In Figure 2, the user is essentially saying they would like to add Item 1 through Item 5.

=SUMIF() and =SUMIFS()

The =SUMIF() and =SUMIFS() functions are used to only add numbers together if certain conditions are met. =SUMIF() is used if there is only one condition to be met, where =SUMIFS() can be used if there are multiple parameters, or just one parameter. As such, =SUMIFS() is the more powerful of the two functions.

Using =SUMIF()

=SUMIF() is used if there is only one parameter to be met. In Figure 3, we are looking to find the total 2021 sales for the state of New Jersey. The function is first evaluating where in Column C the state is “New Jersey”, and then adding the corresponding sales figures in Column D. The formula is adding the sales figures for John, Chris, and Emily; 80 + 200 + 300 = 580. See Figure 3 for more details.

Using =SUMIFS()

=SUMIFS() is used if there are multiple parameters to be met. In Figure 4, we are looking to find the total 2021 food sales for the state of New Jersey. The function is evaluating where in Column C the state is “New Jersey” and where in Column B the Item Cat. is “Food”, and then adding the corresponding sales figures in Column D. The formula is adding the sales figures for John and Chris; 80 + 200 = 280. .

NOTE: The order of the syntax is different between =SUMIF() and =SUMIFS(). In =SUMIF(), the parameter range, and parameter come first, where the sum range comes after. In =SUMIFS(), the sum range comes first and is followed by the parameter ranges and the parameters.

Sum Etiquette

Spreadsheet Etiquette will tell you that sums should typically be at the bottom or right-hand side of the data sets. If at the bottom of a data set, it may be appropriate to add a top border and bold the sum so that it stands out. Sums should rarely ever be in the middle of a data set without any change in formatting.

Excel Vs. Google Sheets

You’re in luck! The sum functions described in this article are identical between Excel and Google Sheets! However, there is one shortcut in Excel that is not available in Google Sheets. In order to quickly sum figures in Excel, all the user needs to do is go to the cell at the bottom of the data set and press Alt + = twice! Excel will automatically highlight the numbers in the column in the formula and add them together!