Summing up is a usual thing which we need to do every now and then. But doing sum for a number of things may become a lengthy process. In this fast world, it might feel like wasting time and you would like to use Excel to do the work within a second. Excel has a variety of ways to do summation in a column. This blog will explain six different methods about how to sum a column in Excel.
The dataset that will be used throughout this article to sum a column in Excel is as follows.
The dataset can be downloaded from here.
How to Sum a Column in Excel (6 Methods)
The dataset above represents a list of products that a company has imported along with the quantity and price of the products. Let’s assume that the company named “X” imports cosmetic products and sells them in their shop. There are long lists of products and quantities are also huge. So, they use Excel to keep the database and do calculations whenever needed.
1. Arithmetic Summation
Let’s start with the most common one. Like pen and paper calculations or calculations using a calculator, we can just sum up, let’s say the price of the products, by taking one by one in Excel too.
Now, this is the most basic and time consuming like the usual used techniques without Excel.
2. Sum with One Click
Isn’t it the easiest one if the sum is done with just one click? Yes, it’s possible to complete the lengthiest summation with just one click.
For doing this, you just need to follow two steps:
- Select the letter of the column (here it is “C”) you want to get the sum off.
- Notice at the bottom of the Excel.
Note: You can find the number of values being added and the average of the values as well which is a bonus.
3. Using AutoSum
The word “AutoSum” itself says that the work will be done automatically. You will find this function in the Home tab. You do not even need to select the range. Follow the following steps:
- Take the cell just under the column which you want to sum up.
- Select AutoSum from the Home tab.
- The Sum function will be automatically shown in the selected cell and the range will also be automatically selected.
- Press Enter and you will be able to see the result in the selected cell.
Note: First select the range and then by pressing the equal sign while pressing Alt will also do the work of AutoSum.
4. Using the Sum Function
Now, if you want to manually write a function to get the summation, you can do that too on Excel.
Pressing Enter will show you the result.
Note: You can either select the range from selecting the first to last value in the column or write the range. It is better to write the range in case of lengthy datasets.
5. Converting Data into Excel Table to Get the Sum of Column
By selecting the dataset and clicking Ctrl+T on the keyboard the dataset can be converted into an Excel table. This will open up an options menu where you will find your dataset range. Tick the “My table has headers” if your table has headers”.
Then, clicking OK will open a new table at the tab called Table Tool. Tick Total Row from there will give the sum of the column.
Note: There is an arrow sign along with the result. From that option, other functions can also be selected if required.
6. Conditional Summation in the Column
All the above methods are, to sum up all the available data. But many times it is required to sum up specific amounts or specific types of data. This can also be done using Excel. There are various types of methods to do the condition-based summation in a column. Let’s have a look one by one.
a. Sum of Filtered Cells in the Column
For this method, firstly you need to filter the table. You can find the Filter option from the Home tab.
First, select Sort & Filter from the Home tab.
A drop-down menu will appear. Select Filter from there.
The table now has an arrow with each column heading.
Clicking on the arrow will bring up menu options.
Selecting desired options will filter out the unwanted values.
I. Let’s say the company owner doesn’t want to take a few items. So, he wants to filter out the products like Gel, Shaving, Face cream. Unselecting the mentioned products will do the work.
Then the table will look as follows:
Now repeating the AutoSum method (Method 3 of this article) will give the desired output.
II. Again, we can select the Text Filters option and this will bring up various options. Let’s select begin with.
This will bring up a new drop-down menu. Here let’s write “F” in the box beside begin with.
Select OK and it will show the table as follows:
Using the AutoSum method (Method 3 of this article) will give the summation of the products.
III. Besides, you can also color the values you want. Let’s color products like Toothpaste, Facewash, Shaving cream.
Then, select the Filter by color and click on the desired color option the table can be modified.
Finally, the output is found using AutoSum (Method 3 of this article).
b. Using SUMIF or SUMIFS formula
I. SUMIF function contains arguments like Range and Criteria. By selecting the desired range and criteria the result can be found.
For this case, the criteria is set for price higher than 200.
II. SUMIFS has more than one Criteria option, unlike SUMIF.
Here, the first criteria is set at higher than $200 and the second criteria is set for less than $600.
This blog contains six methods about how to sum a column in Excel. Using any of the methods to find the required result is suitable and time-saving.