## How to sum a column in Excel?Excel is a tool that requires calculations for data analysis. It is a digital way to store your data in an Excel spreadsheet. To do some analysis, we execute mathematical functions on it. Excel also proposes various inbuilt functions to the users to perform on this Excel sheet. It is easy to calculate the sum of a column in Excel. This chapter will show you how you can calculate the sum of a column or selected cells in Excel. Excel proposes several methods to find the sum of a column - - Sum a column in one click
- Total the sum of a column using AutoSum
- Apply
**Sum**function (mathematical function) to sum a column manually - Use
**Subtotal**to calculate the sum of selected cells - Convert the data to Excel to get the sum of a column
See the detailed methods below: ## Sum a column in one clickIt is one of the easiest methods of Excel to calculate the sum for a column. It is a fast way to get the sum of a column in one click easily. Remember that - this method will only show the total of the selected column but does not save it. The result will show in the
Let's take an example and understand with the help of it: ## Sum of Expenses columnNow, we will see the total sum for the ## Sum of Sell columnNow, see the total sum of one more column, i.e., the ## Total the sum of a column using AutoSumAutoSum is another way to find the sum of a column in an Excel spreadsheet. This method is helpful when you want to keep the result in your Excel table for future use. You can use this AutoSum function of Excel. ## Note: The user can use this calculated sum result in the future when need because Excel keeps it.
We have an Excel spreadsheet containing some products with their price list. We will calculate the total price of these products using the
There was no range selection while calculating the sum for the Price column. We have summed the column for all values. ## Apply the Sum() function to a column manuallyYou can also apply the Sum() function manually to a column instead of an automatic sum. The You are thinking that what is the main objective of applying the Sum function manually and how it is different from AutoSum? Using this function, one can total the sum for the selected cells instead of all values of the column and paste the calculated result anywhere in the Excel sheet.
Sometimes, we require calculating the sum manually if we want to calculate the sum of few selected cells. Use the Let's see how this Sum function apply to a column manually and how it works -
For this example, we will use the same Excel spreadsheet used in the previous method containing some products with their price list. We will calculate the total price of these products using the
=sum(B2:B11) It will total sum the data from B2 cell values to B11 cell.
Here, you see a yellow color error type symbol, indicating to that the formula used for this cell apply to the range of cells. In the Formula bar, you can see the formula applied to the resultant cell. ## Note: SUM() function can work even for hidden and filtered row data.## Use Subtotal to calculate the sum for selected cells
Subtotal function only totals the sum of visible row data of a column. It has a bit complicated steps to see the full functionality of this function. Otherwise, it is as easy as other functions (Sum, AutoSum) used above. We will brief you each step. We will use this table data for the Subtotal function:
To show you the actual use of the Subtotal function, we will first filter the table data based on some conditions. For instance, we will add a filter on the Price column and filter the table data by putting greater than condition, e.g., greater than 350.
## Note: These filters enable separate t filters for string and number type column values.
You can also exclude any row from here by unmarking price column data.
The rows having less than 450 price of the product are hidden by the filter. ## Use AutoSum function to Subtotal a column dataWe will now sum a column data using the Follow the steps to Subtotal a column data:
Here, Excel will keep the result. But if you just want to see the total of visible cells and do not want to keep the result, you can select the range of data in a column and see the sum in the status bar. ## Convert the data to Excel to get the sum of a columnIt is another method to get the sum of a column in an Excel spreadsheet. If you often require to sum a column, you can convert the spreadsheet to an Excel table. It makes it easy for the users to totaling the columns as well as rows. It also allows you to perform some other operations on this list. Follow the below steps how it will be done:
Using this option, you will let the total for each column.
Similarly, you can also find the sum, min, max, or average value for this column by selecting the respective operation from here. ## Sum a column values based on criteria using SUMIFApart from all these methods discussed above, we have one more conditional method to sum a column value. These methods gave the sum of a whole column except the Subtotal method. Sometimes, a user requires totaling the values that satisfy a condition set by the user. Thereby, we will brief you one more Excel function to sum column values based on a condition.
Let's understand with an example -
We have a dataset of products and their price. We will calculate the sum of all products whose price is greater than 550. Use the following SUMIF formula and get the total of the cells which satisfy the defined condition. Follow the steps to calculate sum using the SUMIF function:
## Note: You can use the SUMIF formula on numeric as well as test conditions.Suppose we have to calculate the total sum of all pen drives present in this spreadsheet. In that case, use Pen drive as a condition and include both A and B columns. i.e., Product and Price.
Copy and paste the following SUMIF formula in the formula bar: Press the Besides that, SUMIFS is another function that is almost similar to the SUMIF. Next TopicHow to remove the filter in Excel |