SUM function in Excel: How to use SUM to calculate totals in Excel - SUM function in Excel

The SUM function in Excel is quite commonly used. Below are details on how to calculate the SUM function and related issues in Microsoft Excel.

If your job is office work and you often have to process a lot of data in Excel spreadsheets, you probably already know that this software has many useful functions. From simple functions like addition, subtraction, multiplication and division to complex functions like dates, calculating the smallest and largest numbers, etc. In this article, we will learn together the SUM function in Excel.

These are the main content in this article:

What is the SUM function in Excel?

Simply put, the SUM function adds values. It is designed to return a SUM function of user-supplied values, from individual values, ranges, cell references, or a combination of all three. The SUM function in Excel can handle up to 255 individual references well.

Formula of the SUM function in Excel

Understanding the formula of the SUM function in Excel can help you clearly understand how this function works. The total function formula in Excel is as follows:

The SUM function has two structures so can be written in two ways:

1. =SUM(num1,num2, . num_n). Here " num1,num2, . num_n" represents the numbers we will calculate.
2. =SUM(CellRange1,CellRange2,.CellRange_n). In which: "CellRange1,CellRange2,. CellRange_n" are numeric columns.

Notes in SUM calculation:

1. The SUM function can calculate decimals and fractions.

1. In the SUM calculation: =SUM(num1,num2, . num_n) , if you enter the parameter "num" as a non-number, the SUM function will display the #Name error? Error .

1. And similarly, in the calculation =SUM(CellRange1,CellRange2, .CellRange_n) containing non-numeric content, the SUM function will ignore that value.

1. The SUM function is not a dynamic function. Therefore, if you use the SUM calculation on a range of cells and then filter out certain values, the output of the SUM calculation will not change according to the closest value. Therefore, it is better to use the Subtotal function for this case.

Common SUM functions

The 'members' of the SUM function in Excel all have two things in common: one is that they sum all values ​​in the same way, second is that they all have SUM in the name.

The first function, SUM just sums the input and output values ​​of the result. The other SUM function performs the same task but with some changes. For example, SUMIF adds only values ​​that meet specific conditions. SUMPRODUCT performs an arithmetic operation on the array, then sums the result.

Although all SUM functions calculate sums, that is not their only feature. Understanding how to use other SUM functions can increase your productivity. You do not need to combine SUM with other Excel functions, instead, just use the SUM function that suits your needs.

Some popular SUM functions today:

1. SUM is the most basic member of Excel's sum function.
2. SUMIF sums the range of numbers that meet a specific requirement.
3. SUMIFS calculates sums based on multiple criteria.
4. SUMPRODUCT multiplies a range of numbers, then sums them.

Examples of the SUM function

SUM function in Excel: How to use SUM to calculate totals in Excel - SUM function in Excel Picture 1

In the table above, there are four types of SUM function and below I will explain each specific example:

1. Example 1, sum of integers: =SUM(10,11,19) means adding the numbers 10,11,19 and 40 is the result 40.

2. Example 2, sum of decimal numbers: =SUM(10.2,9.6,2,4) , is simply the addition of 10.2+9.6+2.4 and gives the result 25.8.

3. Example 3, sum of fractional numbers: =SUM(4/2,8/2) . The calculation adds the numbers 4/2 and 8/2 and the result is 6.

4. Example 4, the SUM function fails because it contains non-numeric content and that is also the reason why the calculation produces #Name? Error .

SUM function in Excel: How to use SUM to calculate totals in Excel - SUM function in Excel Picture 2

5. Use the second form of the SUM function [=SUM(B2:B10)] to calculate the sum of the numbers in the number range (B2:B10) instead of the parameters and produce the result 60.

Shortening of the SUM calculation:

Instead of writing the calculation in a simple way, you can use the following:

SUM function in Excel: How to use SUM to calculate totals in Excel - SUM function in Excel Picture 3

First, select the number range then press " Alt + " and the results will be displayed in the next box.

Use other operations in the SUM calculation

Other calculations such as (+, - , / and *) can be used in the SUM function. For example, =SUM(2*4) and the SUM function will output 8. Similarly with the remaining calculations.

In fact, the SUM function will handle all sets of numbers including ranges or calculations. So first thing it will evaluate the result of patch operation if there is no difference then it will produce the result.

Use the SUM Function with other functions

1. It is completely possible to use the SUM function along with other functions. Here is an example calculating monthly average:

SUM function in Excel: How to use SUM to calculate totals in Excel - SUM function in Excel Picture 4

=SUM(A2:L2)/COUNTA(A2:L2)

2. SUM of A2:L2 divided by the number of months in the year in A2:L2 excluding blank cells.

Use the SUM function across multiple worksheets

1. Normally, to sum cells with the same location in pages, you will have to click on that cell on each page and use the "+" sign between the values, but this will take a lot of time and calculation. will error.

=Sheet1!A1+Sheet2!A1+Sheet3!A1

Therefore, this problem will become easier when using the SUM 3D function:

SUM function in Excel: How to use SUM to calculate totals in Excel - SUM function in Excel Picture 5

=SUM(Sheet1:Sheet3!A1)

The SUM function summed cells A1 contained in all sheets 1 through 3.

This method is useful for calculating single pages of months (January to December).

SUM function in Excel: How to use SUM to calculate totals in Excel - SUM function in Excel Picture 6

=SUM(January:December!A2) added all cells A2 on each page from January to December

This 3D method is similarly used in the functions AVERAGE, MIN, MAX,.

1. =AVERAGE(Sheet1:Sheet3!A1)
2. =MIN(Sheet1:Sheet3!A1)
3. =MAX(Sheet1:Sheet3!A1)

Common problems and solutions:

Case 1: The SUM function displays #####.

Please check the column width, as ##### indicates there is not enough space to display the results.

Case 2: The SUM function displays text and does not produce results. Or only show numbers but not decimals.

Check the column format again. Go to Format and select Number to choose the format you want. If after formatting, the text displayed in the cell does not change, you should press F2 and enter.

Case 3: The SUM function does not update.

You must make sure that Calculation is set to Automatic mode . First go to Formula , select Calculation options . Then press F9 to automatically update the page.

Case 4: Some values ​​are not used in the SUM function.

Only values ​​or ranges that contain numbers can be placed in the function. Empty cells, logical functions, or text will be removed.

Case 5: The function produces the result #NAME? Error.

There is a spelling error here. For example, =sume(A1:A10) instead of =SUM(A1:A10).

Frequently asked questions when using the SUM function in Excel

How to show more/fewer decimal places? You can change the number format. Select the cell or range in question and use Ctrl + 1 to open the Format Cells dialog box, then click the Number tab and choose the format you want, making sure to specify the number of decimal places you want.

How to add or subtract time? You can add and subtract time in a number of different ways. For example, to calculate the difference between 8:00 a.m. and 12:00 noon for payroll purposes, you would use =("12:00 PM"-"8:00 AM")*24, subtracting the end time start time. Note, Excel calculates time as a fraction of a day, so you need to multiply by 24 to calculate total working hours. In the first example, use =((B2-A2)+(D2-C2))*24 to calculate the total number of hours from start to finish, minus the lunch break (8.5 hours total). .

How to get the difference between dates? Similar to time, you can add and subtract dates. This is an extremely common example of how to calculate the number of days between two different dates. The formula for using the SUM function in Excel here is very simple: =B2-A2. The 'key' to process the information here is to take the ending date/time and subtract the starting date/time.

How to sum the displayed cells? Sometimes when you manually hide rows or use AutoFillter to show only certain data, you also want to count only the visible cells. You can use the SUBTOTAL function. If you're using a total row in an Excel table, any function you choose from the Total drop-down menu will automatically be entered as the subtotal.

Micah Soto

Update 30 October 2023