How to Add Up Columns in Excel (12 Methods)

In Microsoft Excel often we need to calculate the sum. For example, you have a dataset of sales and you need to summarize the total sales. For that, you need to add up columns. Today I am going to explain how you can add up columns in excel. You can check this link too.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


12 Suitable Methods to Add Up Columns in Excel

There are many ways to add up columns. Consider a dataset of sales of different products in a week.

How-to-Add-Up-Columns-in-Excel


1. Use SUM Function to Add Up Columns

In the following with the use of the SUM function, I am going to add up column C. Let’s see how it’s done!

Steps :

  • Select a cell where you want your output.
  • Apply the formula-
=SUM(C5:C9)

Where,

  • The SUM function sums the selected values.
  • (C5:C9)= Range of cells.
  • Press Enter.

Use SUM Function to Add Up Columns

  • Thus, you will find the sum of columns in the desired cell.

Use SUM Function to Add Up Columns

Read More: How to Add Two Columns in Excel (2 Easy Methods)


2. Add Up Entire Columns in Excel

You can also add up an entire column with the SUM function.

Steps:

  • Choose a cell to show output. We want to add up the entire column D.
  • Apply the formula-
=SUM(D:D)

Where,

  • The SUM Function sums the selected column.
  • (D:D)= Range for the column.

Add Up Entire Columns in Excel

  • Click Enter to continue.

How to Sum Entire Column in Excel (9 Easy Ways)

Add Up Entire Columns in Excel

  • So, we got our total sum for the entire column.

Read More: How to Sum Entire Column in Excel (9 Easy Ways)


3. Apply AutoSum Feature to Add Up Columns in Excel

Excel has some built-in features for summing columns or rows. With the help of the AutoSum shortcut, you can sum columns with just a single click. It’s that easy.

Steps :

  • Select the column that you want to add up.
  • Go to Home > Editing > AutoSum.

Apply Autosum Shortcut to Sum Columns in Excel

  • Below the selected range you will find your desired result.

Apply Autosum Shortcut to Sum Columns in Excel

Read More: How to Total a Column in Excel (7 Effective Methods)


4. Apply Keyboard Shortcut to Add Up Columns in Excel

Well, there is another easy trick with a keyboard shortcut. In the previous section, we have added a single column. Now, we will add up multiple columns with this keyboard shortcut.

Steps:

  • Select multiple cells with data.
  • Press ALT+

Apply Keyboard Shortcut to Add Up Columns in Exel

  • You will see the sum values under every column.

Apply Keyboard Shortcut to Add Up Columns in Exel


5. Add Up Multiple Columns at Once

Adding up multiple columns is a simple task to do at once. Following section, we will add up multiple columns with two easy tricks.

Step 1:

  • Select a cell to get the result.
  • Type the formula-
=SUM(C5:E9)

Where,

  • The SUM function sums up the selected multiple columns.
  • (C5:E9) =Range of values.

Add Up Multiple Columns at Once

  • Press Enter.
  • The result shows the sum of all cells in the selected range.

Add Up Multiple Columns at Once

Step 2:

In the previous, we have selected cells manually. We can also do that with the help of a keyboard too.

  • Select a cell for output.
  • In that cell, type: =SUM(
  • Now, from the dataset select a cell from where the range starts.
  • Hold “Shift” and click the arrows from the keyboard to select the desired range of cells.

Add Up Multiple Columns at Once

  • Close the range of cells with a bracket.
  • Press Enter.

Add Up Multiple Columns at Once

  • The output cell will show the results for the selected range only.

Read More: Sum Multiple Columns Based on Multiple Criteria in Excel


6. Add Up Multiple Columns Except for Header

Sometimes you will see a header in a dataset. As the header does not contain any numeric value, it becomes difficult to add up columns. In this method, I am explaining to add up columns except for a header.

Steps:

  • Select a cell to apply formulas (C11)
  • Type the formula-
=SUM(D5:D104876)

Where,

  • The SUM function sums up the range.
  • D104876” is used as excel has 10,4876 cells in a column.

Add Up Multiple Columns Except for Header

  • Press Enter.

Add Up Multiple Columns Except for Header

  • That’s how we can add up all the values in a column except for a header.

7. Add Up Non-Contiguous Columns in Excel

In the following method, I will describe adding up non-contiguous columns.

Steps:

  • Choose a cell for output.
  • Apply the formula-
=SUM(SUM(D:D),SUM(E:E))

Where,

  • (SUM(D:D),SUM(E:E)= It represents the summing values of the entire columns D and E.
  • The SUM function sums the values within the given range.

Add Up Non-Contiguous Columns in Excel

  • Click Enter to get the result

Add Up Non-Contiguous Columns in Excel

  • Thus we can add up non-contiguous columns very easily.

8. Use Filter Feature to Add Up Filtered Cells in a Column

With the help of the Filter feature, you can filter cells and then add up columns.

Step 1:

  • Select the header of the dataset to filter.
  • Go to “Data” and click “Filter”.

Use Filter Feature to Add Up Filtered Cells in a Column

  • Now you can filter your data according to your choice.

Use Filter Feature to Add Up Filtered Cells in a Column

Step 2:

  • Click on the arrow mark of the first column.
  • A window appears with different options.
  • Remove tick() marks from the “Tuesday” and “Wednesday” options.

Use Filter Feature to Add Up Filtered Cells in a Column

Step 3:

  • This way, we will get our filtered data by removing sales of “Tuesday” and “Wednesday”.
  • It’s time, to sum up, our filtered data. Select all the values from the dataset.
  • Home > Editing > Autosum.

Use Filter Feature to Add Up Filtered Cells in a Column

  • Here it is. The sum in each column shows the data with filtering.

Use Filter Feature to Add Up Filtered Cells in a Column

Read More: How to Sum Columns in Excel When Filtered (7 Ways)


9. Select Multiple Columns and Add Up in Status Bar

When we select multiple cells in columns and rows, excel automatically shows the sum, count of cells, and average values in its status bar.

Step 1:

  • Select a column.
  • In the status bar, you will find the sum value for the entire column.

Select Multiple Columns and Add Up in Status Bar

We can also get the add-up values for multiple columns too.

Step 2:

  • Select multiple columns.
  • In the status bar, it will show the sum of all values selected in multiple columns.

Select Multiple Columns and Add Up in Status Bar

Read More: Sum Every nth Column in Excel(Formula and VBA Code)


10. Add Up Only the Visible Cells in a Column

When we filter a dataset and sum the filtered values, it will not show the results for the filtered values. The formula will take the values which are not visible. This becomes a problem when we try to add up only the visible cells. But this will not be a problem anymore! I will show you a method by which you can add up only the visible cells in a column.

Consider a filtered dataset containing sales of different products.

Add Up Only the Visible Cells in a Column

Step 1:

  • Select a cell where the output will be shown.
  • Type the formula-
=SUM(D:D)

Where,

  • The SUM function adds values.
  • (D:D) is the range.

Add Up Only the Visible Cells in a Column

  • Press Enter.

Add Up Only the Visible Cells in a Column

You will see all the values added up. But the SUM function took values from the cells which are not visible in the worksheet. That is not the desired result we are looking for.

The solution is we have to use the AGGREGATE function to get the result for the visible cells only.

Add Up Only the Visible Cells in a Column

Step 2:

  • Select the cell where the formula will be applied.
  • Apply the following formula-
=AGGREGATE(9,3,D7:D9)

Where,

  • The number used in the formula “9” tells the AGGREGATE function to sum the selected cells.
  • The number “3” is used to ignore hidden rows from the range.
  • Click Enter.

Add Up Only the Visible Cells in a Column

  • Thus we will get the sum values for visible cells only.

11. Create an Excel Table to Add Up Columns in Excel

To add up columns you may need to create an excel table.

Step 1:

  • Select the whole dataset (B4:E9)
  • Go to Insert > Table.

Create an Excel Table to Add Up Columns in Excel

  • A new window will appear named “Create Table”.
  • Click “My Table has headers”.

A new window will appear named “Create Table”. Click “My Table has headers”.

  • Click OK to create the table
  • We will find our table created. Now, we will add up columns from the table.

A new window will appear named “Create Table”. Click “My Table has headers”.

Step 2:

  • Select any row from the dataset.
  • In the “Table Design” ribbon click tick () on the “Total Row”.

A new window will appear named “Create Table”. Click “My Table has headers”.

  • You will see a new column showing the total values of the selected column.

A new window will appear named “Create Table”. Click “My Table has headers”.

Read More: How to Sum Columns in Excel Table (7 Methods)


12. Add Up Columns Based on Criteria in Excel

We often add up columns in a dataset based on criteria. We can do it with the help of the SUMIF function. In the following, I am going to describe how we can add up columns based on criteria.

Steps:

  • From the dataset select a cell to apply the formula.
  • Type the formula-
=SUMIF(E5:E9,”>70”,E5:E9)

Where,

  • The SUMIF function is used to sum with a given condition in the range.
  • >70” is the condition as we want numeric values below 70 from the dataset.
  • (E5:E9) is the range.
  • Press Enter.

Add Up Columns Based on Criteria in Excel

Here we got our added up values with the given condition.

Add Up Columns Based on Criteria in Excel

Read More: How to Sum Columns by Color in Excel (6 Easy Methods)


Thinks to Remember

  • When you create a table from the “Table Design” ribbon. It will add up only the visible cells.
  • “#VALUE!” -Sometimes, This may show in the output results. That means you have non-integers in the sum formula.
  • “#REF!”– This means previously referenced row(s) or column(s) do not exist anymore.

Conclusion

I have tried to cover all the methods in this article. Hope you will find all the solutions. Feel free ta ask us any questions if you find any problems. Enjoy!


Related Article

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo