Calculating the sum/SUM, SUBTOTAL, sum of multiple columns

To calculate the sum of numbers in cells, we can use the SUM function and the SUBTOTAL function.

=SUM(number1, [number2…])

[number1] = Select cell range to find the sum for.
[number2] = Not obligatory. Select when there is another cell range to find the sum for.

Definition of function A function to find the sum of numbers in a specified range
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=SUBTOTAL(function_num, ref1, [ref2…])

[function_num] = Select from numbers between 1~11, or 101~111. The meaning for each number will be explained below, in ‘About the [function] of the SUBTOTAL’.
[ref1] = Select cell range to aggregate.
[ref2…] = Not obligatory. Select when there is another cell range to aggregate.

Definition of function A function that aggregates numbers based on a selected method of aggregation in a specified range
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

About the [function] of the SUBTOTAL

Here we select the method of calculating the subtotal. The difference between the numbers is that 1~11 include the rows hidden manually, but in 101~111, they are not.

【List of calculating methods】

Value selected
(includes hidden rows)
Value selected
(excludes hidden rows)
Function
1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP

 

Example of use 1) Calculate the sum (total) in a specified range/SUM function

The following list shows the yearly sales of a company. We will calculate the sum of the first 6 months (January~June) in cell E4.

① Calculate the total sales amount for the first 6 months using the SUM function.

(1) [number1] : Select cell range with the sales amount for January~June, [B4:D4].
=SUM(B4:D4)

 

Example of use 2) Calculate the sum of selected cells/SUM function

Using the same table, we will now calculate the “yearly sales total” in cell C7.

① Calculate the yearly sales total (by adding up the 2 totals of the first and last 6 months) using the SUM function.

(1) [number1] :  Select cell range with the sales total and consumption tax total for the first 6 months, [E4:E5].
(2) [number2] :  Select cell range with the sales total and consumption tax total for the last 6 months, [I4:I5]
=SUM(E4:E5,I4:I5)

 

Example of use 3) Calculate the total of selected cells/SUBTOTAL function

Next, we will use the SUBTOTAL function which also calculates the sum.

① Calculate the total sales amount for the first 6 months using the SUBTOTAL function.

(1) [function_num] : We choose method ‘9’ as we are using the SUM function.
(2) [ref1] : Select cell range with the sales amount for January~June, [B4:D4].
=SUBTOTAL(9,B4:D4)

 

POINT!

One great thing about the SUBTOTAL function is that when a cell also summed up using the SUBTOTAL function is included in [ref1], the function recognizes it as the subtotal.
For example, in the table below, cell ‘E4’ and ‘I4’ have formulas using the SUBTOTAL function.
Based on that, if we use the SUBTOTAL function to calculate the yearly sales total in cell ‘J4’, cell ‘E4’ and ‘I4’ will be recognized as the ‘subtotal’ and will only add up those 2 cells. (On the other hand, if we use the SUM function and select [B4:I4], it will literally calculate all those cells.)

The formula to cell ‘J4’

=SUBTOTAL(9,B4:I4)

 

Situations when SUM does not calculate/’text string’ data

Even though it looks like a group of numerical values, there are times when SUM does not do its job. This can happen when the numbers are not recognized as numbers by Excel, but as ‘text strings’ also known as strings, or just text. We will explain this in detail below.

The table below shows the exam grades of a student. He typed each of his scores and tried to get his total in cell C8.

If we look at his table, we can see that the total is calculated to a ‘0’.
This is because Excel automatically distinguished cell range [C4:C7] as

a character (word), and not a number

Because the SUM function can only calculate numerical values, the result was displayed as ‘0’ (=not able to calculate).
We can solve this problem with the following steps.

How to check the format)

What can we do with ‘Format cells’?
The tab ‘Format cells’ allows us to check and change the display of data including text position, font type and size, and color of cells.

 

① Right click on cell which we want to check, and choose ‘Format cells’ from tab.

 

② If we look at the category on the left, ‘Text’ is highlighted in blue.

 

This means that this cell, even though it looks like a numerical value to us, is recognized as a ‘text’ by Excel.
It is not common for this to happen yet sometimes the values are changed to the wrong category without us being aware of it.
Below, we will look at how to change it back to the correct category.

Example of correcting the format)

① Right click on cell needed to be corrected, and choose ‘Format cells’ from tab.

 

② Choose ‘Number’ from the category (on the left) and press [OK].

The process is complete.
The SUM function only calculates numbers, but once we know how to use it, it is useful in many ways, like using text in the table on purpose.

 

Calculating the sum with a condition
/SUMIF→select text string, includes ~ (wildcard), not ~

To calculate the sum of cells that meet a certain condition, we use the SUMIF function.
=SUMIF(range, criteria, [sum_range])

[range] = Select cell range to find if it meets condition.
[criteria] = Input condition (number or text).
[sum_range] = Select cell range to find the sum for.

Definition of function A function that searches in the [range] to find cells that meet the [criteria] and calculates the sum of the cell in the same row (or column) of the [sum_range].
EXCEL version  Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

Example of use 1) Evaluate sum through text selection

Below is the income list of employees in a company. We will calculate the total income for Sales team 1 in D13.

① Calculate income total of employees from ‘Sales team I’ using the SUMIF function.

(1) [range] : Select cell range with the specific text, [B4:B9].
(2) [criteria] : Surround the text (i.e. the condition) ‘Sales team I’ with ” (double quotations). → “Sales team I”
(3) [sum_range] : Select cell range to find the sum for, [D4:D9].
=SUMIF(B4:B9,”Sales team I”,D4:D9)

 

Example of use 2) Evaluate sum through text selection : ‘Including ~’ (Wildcard)

As the next step, we would like to calculate the income total for both sales team I and II. For this process, ‘wildcard characters’ are useful.

What is a wildcard character?

Wildcard characters are useful to search for words which include the specified character, by adding it in the front, the back, or both front and back.

Symbol Definition
* Allows more than one character
? Allows one character

 

 

 

We will now try to calculate the income total for both Sales team I and II.

① Use the SUMIF function and calculate income total of employees from the Sales team (both I and II).

(1) [range] : Select cell range with the specific text, [B4:B9].
(2) [criteria] : Add the wildcard character ‘*’ behind the word ‘Sales team’ and surround it with ” (double quotations). → “Sales team*”
(3) [sum_range] : Select cell range to find the sum for, [D4:D9].
=SUMIF(B4:B9,”Sales team*”,D4:D9)

Adding the wildcard character ‘*’ means there is an arbitrary word after ‘Sales team’ (Sales team ~).

Example of use 3) Evaluate sum excluding the specified condition/ the ‘not’ condition

Using the same income list, we will evaluate the sum for all departments except for ‘Sales team I’.

① Use the SUMIF function and calculate income total of employees for all departments except for Sales team I.

(1) [range] : Select cell range with specific text, [B4:B9].
(2) [criteria] : Add ‘<>’ (symbol representing not/excluding ~) in front of ‘Sales team I’, and surround it with ” (double quotations). → “<>Sales team I”
(3) [sum_range] : Select cell range to find the sum for, [D4:D9].
=SUMIF(B4:B9,”<>Sales team I”,D4:D9)

The symbols that we use for search…

There are other symbols other than ‘<>’ which are all useful when setting up a formula.

Symbol Definition
< Smaller than the selected number
<= Equal to or smaller than the selected number
> Bigger than the selected number
>= Equal to or bigger than the selected number
<> Not equal to selected number (besides/other than)
= Equal to selected number

 

POINT!

Due to the fact that the SUMIF function sums up values within the [range] that meet a specific condition, each value of the [range] and the [sum_range] have to be in the same row (or column).

 

Multiple conditions, ‘or’ condition for SUMIFS/SUMIF functions

To set up a condition and calculate the sum of numbers that meet the condition we use the SUMIF function, but when there are multiple conditions, we use the SUMIFS function.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2…])

[sum_range] = Select cell range to find the sum for.
[criteria_range1] = Select cell range to see if it meets condition or not.
[criteria1] = Input condition (number or text).
[criteria_range2, criteria2…] = Use when there is more than 1 condition.

Definition of function A function that searches within the [criteria_range1, 2…] to find cells that meet the [criteria 1, 2…] and calculates the sum of the cell in the same row (or column) in the [sum_range].
EXCEL version Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

Example of use 1) Set up multiple conditions and find sum

Below is the same income list in a company which we used to explain the SUMIF function. A new column ‘Years at work’ is added, and we would like to calculate the income total for condition 1 = ‘Sales team I’ and condition 2 = ‘5 or more years at work’.

① Calculate the income total with condition 1 = ‘Sales team I’, and 2 = ‘5 or more years at work’, using the SUMIFS function.

(1) [sum_range] : Select cell range to find the sum for, [E4:E9].
(2) [criteria_range1] : Select cell range to see if it meets 1st condition, [B4:B9].
(3) [criteria1] : Surround the word ‘Sales team I’ with ” (double quotations) → “Sales team I”
(4) [criteria_range2] : Select cell range to see if it meets 2nd condition, [C4:C9].
(5) [criteria2] : Use ‘>=’ to express ‘5 or more years’ and surround it by ” (double quotations) → “>=5”
=SUMIFS(E4:E9,B4:B9,”Sales team I”,C4:C9,”>=5″)

 

Example of use 2) Set up multiple conditions and find sum that meets either/the ‘or’ condition

We can use the SUMIFS function only when ‘the data meets multiple conditions’. What happens if we want to find out the sum for when ‘the data meets either condition‘?
This is more difficult, yet possible with the combination of  ‘{}’ (curly braces) and the SUM function.

We will use the same income list and try to set up a formula to find out the income total for condition 1= ‘either Sales team 1 or Sales team 2’, and condition 2= ‘5 or more years at work’.

① Calculate the income total with condition 1 = ‘Sales team I or II’, and 2 = ‘5 or more years at work’, using the SUM function.

(1) Continue SUM formula by adding a SUMIFS function. → =SUM(SUMIFS(
(2) [sum_range] : Select cell range to find the sum for, [E4:E9].
(2) [criteria_range1] : Select cell range to see if it meets 1st condition, [B4:B9].
(3) [criteria1] : Use ” (double quotations) to surround and a , (comma) to divide. Surround everything with {} (curly braces). → {“Sales team I”,”Sales team II”}
(4) [criteria_range2] : Select cell range to see if it meets 2nd condition, [C4:C9].
(5) [criteria2] : Use ‘>=’ to express ‘5 or more years’ and surround it by ” (double quotations) → “>=5”
(6) Close brackets of SUMIFS function and SUM functions. → ))
=SUM(SUMIFS(E4:E9,B4:B9,{“Sales team I”,”Sales team II”},C4:C9,”>=5″))

 

POINT!

Why we use {} (curly braces) and the SUM function

The {} (curly braces) symbolize ‘either of the conditions’ (priority from the left).
If we use only the SUMIFS function,

=SUMIFS(E4:E9,B4:B9,{“Sales team I”,”Sales team II”},C4:C9,”>=5″)

the income total will be ‘250,000’, as it prioritizes ‘Sales team I’.
To avoid this, we surround the formula with ‘SUM ()’.

=SUM( SUMIFS(E4:E9,B4:B9,{“Sales team I”,”Sales team II”},C4:C9,”>=5″) )

By doing this, both ‘Sales team I and 5 or more years at work’ and ‘Sales team II and 5 or more years at work’ will be summed up.
However, we must keep in mind that not all functions can be used like above.

 

The AutoSum

We will look at a useful tool called AutoSum, which allows us to sum up values in a simple way.

‘AutoSum’ is a tool on the upper left displayed as ‘∑’ which calculates the sum/total of ordered numerical values by automatically identifying the range (to sum up).

The table below is a sales report for a company. We will use AutoSum to calculate the total sales amount for the first 6 months in cell F4.

 

Example of use 1) The basic way

① Select corresponding cell to display the results of the total, in this case, cell ‘F4’.

 

② Click on the ‘Formulas’ tab in the ribbon.
③ Click ‘∑’ from the ‘Formulas’ tab.

 

④ The SUM function will appear automatically, displaying cell range to find the sum for.

 

⑤ If there is no problem with the formula, press ‘ENTER’ on keyboard.

Results of the AutoSum)

 

POINT!

 

The AutoSum is simple, but sometimes it highlights the wrong range.
We will look at the example below to see how to fix the cell range.

When the cell range (to find the sum for) is not correct in AutoSum)

The table below shows the exam results of students in a school. Cell H4 and H5 are correctly summed up, but when trying the same with cell H6, the range identified was as follows. → =SUM(H4:H5)

The above occurred as the AutoSum prioritizes ‘the vertical sum’. If this happens, we can correct the cell range manually. Be careful not to press ‘Enter’ before correcting.

How to correct the AutoSum range)

① Highlight the correct range to [C6:G6] while the cell is flashing.

② Press ‘ENTER’ on keyboard.

The correcting process is now complete.

 

Example of use 2) Calculate the sum in a cluster

In cases like below, AutoSum can calculate the sum in a cluster/as a group.

① Highlight cells to sum up and cells to display the results on.

② Click ‘∑’ (AutoSum) and press ‘ENTER’.

Results of the AutoSum in a cluster) 

The AutoSum can automatically calculate the sum if the cells to sum up and the cells to display the results on are next to each other.