How to Sum a Column in Excel (6 Methods)

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.

Sample dataset about how to Sum a column in Excel

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.

Arithmetic sum to find sum in a column

Now, this is the most basic and time consuming like the usual used techniques without Excel. 

=C4+C5+C6+C7+C8+C9+C10+C10+C11

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.

Finding sum with one click

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:

Selecting cell for applying AutoSum in column

  • Take the cell just under the column which you want to sum up.
  • Select AutoSum from the Home tab.

Finding AutoSum from Home tab

  •  The Sum function will be automatically shown in the selected cell and the range will also be automatically selected.

AutoSum function to find sum in column

  •  Press Enter and you will be able to see the result in the selected cell.

AutoSum result of sum in column

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.

SUM function to find sum in the column

Pressing Enter will show you the result.

Sum result of a column

=SUM(B4:B11)

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”.

Excel table to find sum in a column

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.

Table tool to find sum of a column

Sum of a column using Excel table

Note: There is an arrow sign along with the result. From that option, other functions can also be selected if required.

Showing various options in the drop down menu of AutoSum

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.

Filter to find sum of a column

A drop-down menu will appear. Select Filter from there.

The table now has an arrow with each column heading.

Filtering cells to find sum of a column

Clicking on the arrow will bring up menu options.

Filtering specific values for sum in the column

 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.

Filtering selected cells to find sum in the column

Then the table will look as follows:

Filtered table for finding sum in the column

Now repeating the AutoSum method (Method 3 of this article) will give the desired output.

Sum in a filtered column

II. Again, we can select the Text Filters option and this will bring up various options. Let’s select begin with.

Using text filter to find sum in a column

This will bring up a new drop-down menu. Here let’s write “F” in the box beside begin with.

Text filter selecting to find sum in a column

Select OK and it will show the table as follows:

Filtered by text to find sum of a column

Using the AutoSum method (Method 3 of this article) will give the summation of the products.

Summation of filtered by text from a column

III. Besides, you can also color the values you want. Let’s color products like Toothpaste, Facewash, Shaving cream.

Colored specific values for finding filter by colored sums from the column

Then, select the Filter by color and click on the desired color option the table can be modified.

Selecting filter by color to find sum of colored product in the columnFiltered by color to find sum from a column

Finally, the output is found using AutoSum (Method 3 of this article).

Sum from a column by using filter by color

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.

SUMIF to find sum in a column

For this case, the criteria is set for price higher than 200.

=SUMIF(D4:D11,”>200″,D4:D11)

II. SUMIFS has more than one Criteria option, unlike SUMIF.

SUMIFS to find sum of criteria based column

Here, the first criteria is set at higher than $200 and the second criteria is set for less than $600.

=SUMIFS(D4:D11,D4:D11,”>200″,D4:D11,”<600″)

Conclusion

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.

Tags:

Syeda Fahima Nazreen

Hello People! This is Syeda Fahima Nazreen. I have completed my Bachelors in Science in Electrical and Electronic Engineering. I love to do research and work anything related to technology which includes research and development. I feel great to share my knowledge with you people and your thoughts and opinions about my writing is highly appreciated by me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo