Technology

Share 12 Simple Yet Unknown Ways to Calculate Sum in Excel

Knowing how to calculate sums in Excel is the most basic thing that any Excel user can do. But there are many different ways to calculate sums in Excel that not everyone knows. So let's find out together with FPT Shop.

Administrator

18 October, 2023

203

If you frequently store data such as price lists or cost tables in Excel on your laptop, tablet… while on the move, you may need a quick way to summarize prices or calculate the total amount. Today, FPT Shop will guide you on how to calculate totals in Excel easily. The interesting thing is that there are up to 12 ways to calculate totals in Excel, from simple to complex, you will also find useful tips to calculate the total of an entire column as well as calculate the total of filtered cells in Excel.

12 ways to calculate totals in Excel that not everyone knows

It may seem that calculating totals in Excel is quite simple and almost any Excel user can do it. However, depending on the type of data, the calculation requirements, choosing the right way to calculate totals in Excel is not something that everyone knows. Below, we will explore in detail 12 very interesting ways to calculate totals in Excel, which can be applied to different types of data and different calculation requirements.

1. Calculate totals in Excel using arithmetic formulas

One of the simplest ways to calculate totals in Excel is to use basic arithmetic calculations. You need to place the mouse cursor in the cell where you want the calculation result and enter the plus sign and add up the cells that contain the data you want to calculate.

Very simple, right? However, with a large data set, this process will take a lot of time. But to be more clear, we will still explain the steps to perform the calculation in Excel through the example below.

First, go to cell C12 and enter the following formula: =C5+C6+C7+C8+C9+C10+C11

So we have entered each cell separately and added them together to get the total. Clearly, you get the expected result with simple addition after pressing the Enter key.

2. Calculate totals in Excel using the status bar

One of the easiest and quickest ways to calculate totals in Excel, without having to enter a complete formula like the one above, is that you can see the total of a column from the Excel status bar. This is a trick that not everyone knows, used to calculate totals but does not show in any calculation cell in Excel. To learn how to calculate totals in Excel through the status bar, follow these steps.

Click the left mouse button on the column that contains the data you want to calculate and the selected column will be highlighted by Excel. In the bottom right corner of the Excel status bar, it will provide three important pieces of information, including Average, Count, and most importantly, Sum. The Sum item is the sum of the numbers in the selected column and this is also the addition function in Excel.

3. Calculate totals in Excel using the AutoSum feature

For those who like to use command buttons, surely they are already familiar with how to calculate totals in Excel using the AutoSum feature. This can also be seen as the addition function in Excel. With this feature, you can calculate the sum of a column with just a click of the mouse without having to perform any complex commands. Here’s how to calculate totals in Excel using AutoSum with just one click.

Step 1: Still in the Excel data table that needs to calculate the total. You just need to click on the last cell of the column or row that needs to be totaled. As in the example below is cell C12.

Step 2: Then, on the ribbon toolbar, access the Home tab and click the AutoSum button in the Editing feature group, and choose the Sum option.

Lastly, you will receive the result as shown above.

4. Calculate totals in Excel with a single column

When using an Excel data table, you may often encounter cases where a column has many rows and a lot of data in it. If you use the manual addition or move down to the last cell of that data column to calculate the total, it will take a lot of time.

For this case, using the method to calculate totals in Excel for a single column will be the best solution and with just a few simple steps without having to worry about where the final data is located. Of course, to calculate totals in Excel for a single column, we will also use the addition function in Excel here. The steps to be performed as follows will help you calculate totals in Excel for a single column.

Step 1: First, click on cell F5 and then enter the following formula.

=SUM(C:C)

Here, C:C represents column C and the SUM function to calculate the arithmetic sum of the column. This selection means that Excel will automatically calculate the sum of the data in column C from top to bottom of the data table automatically without having to worry about where the final data is located.

Step 2: Finally, press Enter to get the result as above.

5. Calculate totals in Excel using the SUM function

One of the popular functions to calculate totals in Excel is the addition function in Excel – SUM. If you are new to Excel and need to know how to calculate totals in Excel with the simplest function, this is the function to know. This is an integrated function in Excel to calculate the total.

Step 1: Open the Excel data table that needs to calculate the total and move to the cell to calculate the total.

Step 2: As in this example, I select cell C12. Then enter the following formula.

=SUM(C5:C11)

In the formula above, C5:C11 is the range of data to calculate the total in Excel.

Step 3: Finally, press Enter to get the desired result.

6. Calculate totals in Excel by converting data into a table

This is a very interesting way to calculate totals in Excel and can be said to be “unusual” to calculate totals. The solution in this case is to convert your data into an Excel table and estimate the total value. Follow the steps below to calculate totals in Excel.

Step 1: First, select the entire range of the Excel data table you want to filter and then select the Insert tab and click the Table button in the Tables tool group.

Step 2: The Create Table window will immediately appear. Click the option My tablet has headers and click the OK button.

Step 3: Next, go to the Table Design tab and click the Total Row option in the Table Style Options feature group. Then you will receive the result of calculating the total at the bottom.

7. Calculate totals in Excel based on another column

If you want to find the total of a column based on another column, you can do it using the SUMIF function. This will be another way to calculate totals in Excel that you can use when needed. This function checks the given argument and then returns the total. It is a combination of the addition function in Excel is SUM and the IF function. In this example, to find the total revenue of the employee named Tuấn Ngọc, you do the following:

Step 1: Move to cell C12 and enter the following formula

=SUMIF(B5:B11,”Tuấn Ngọc”,C5:C11)

This syntax checks the name “Tuấn Ngọc” within the range of cells B5:B11, then calculates the sum of the values of John in the range C5:C11.

Step 2: End by pressing the Enter key to get the result as above.

8. Calculate totals in Excel when a column has empty cells

If you have a column with empty cells, you can use the SUMIFS function to calculate totals in Excel. This will be another way to calculate totals in Excel that allows you to calculate the total in a range of data where there are empty cells. For this case, you need to put conditions in those empty cells and use the SUMIFS function. Follow the steps below to perform.

Step 1: First, enter the following formula in cell C12.

=SUMIFS(C5:C11,B5:B11,””)

This function will check the values in the range of cells C5:C11, then match the criteria of ” ” (empty), which means the range of cells B5:B11 for empty criteria.

Step 2: Finally, you will get the result after pressing Enter.

9. Calculate totals in Excel with a single criterion

For this case, if you want to know how to calculate totals in Excel when you have a data table, in which you need to calculate the total for a single criterion in the table. As in the example below, if you want to calculate the total for salespeople with a value over 1500 USD. For this criterion, you can use the SUMIF function to perform.

Step 1: At cell C12, we have entered the formula.

=SUMIF(C5:C11,”>1500″,C5:C11)

In which the formula above will only use the range of cells C5:C11, in which it searches for the criterion >1500 and calculates the sum of the values of the cells that satisfy that condition.

Step 2: Finally, press Enter to get the result as above.

10. Calculate totals in Excel with multiple criteria

For the case you want to know how to calculate totals in Excel when using multiple criteria, then you can use the SUMIFS function. This function checks and returns the output for multiple criteria. As in the example below, to know the total sales revenue from 1000 USD to 1500 USD, follow the steps below.

Step 1: First, at cell C12, enter the following formula.

=SUMIFS(C5:C11,C5:C11,”>1000″,C5:C11,”<1500")

In which the SUMIFS function will use the range to search and total is C5:C11, with criteria range of C5:C11 for criterion 1 (“>1000”) and criterion 2 (“<1500").

Step 2: Finally, press the Enter key to calculate the total of the column based on multiple criteria.

11. Calculate totals in Excel with multiple columns

In the last part of this article on how to calculate totals in Excel, if you want to estimate the total for multiple columns in a data table, it will be a simple method, similar to the above methods. Because you just need to use the addition function in Excel, which is the SUM function.

Step 1: In cell E12 enter the following formula.

=SUM(C5:E11)

In which the SUM function will use the search range and the sum of three adjacent columns (C, D, and E).

Step 2: Finally press Enter and you will receive the desired result.

12. Calculate totals in Excel using filters

The Filter feature in Excel is commonly used with large data tables and is used to filter data according to various criteria. But users can also use this feature to calculate totals in Excel easily. This is an operation commonly used in business activities, when many departments contribute together to the overall sales. Below is how to calculate totals in Excel using the Filter feature quickly.

Step 1: On the main Excel screen, select the data range you want to filter, then select the Data tab and click the Filter icon to enable the Filter option.

Step 2: After clicking Filter, you will see a drop-down box appear. Click on it and select the data you want to filter, as in the example we will select “Marketing”.

Step 3: After Excel filters the result, we just need to use one of the 2 ways to calculate totals as mentioned above to find out the total number of sales that the Marketing department has achieved, very simple and quickly.

So FPT Shop has just introduced to you the different ways to calculate totals in Excel, from simple solutions like pure addition to using the popular SUM addition function in Excel, but more importantly, the methods to convert data into tables for calculating totals or using the Filter feature with large data tables. If you find this article useful, leave a comment below the article. And remember to regularly visit the FPT website to update interesting and useful tips every day to serve your work and study. Thanks for your following.