# How to Sum Columns in Excel (7 Methods) - ExcelDemy (2023)

Get FREE Advanced Excel Exercises with Solutions!

This article shows how to sum columns in Excel using 7 easy but effective ways. Applying these methods is going to speed up data analysis and help you to explore your dataset more confidently in Microsoft Excel.

7 Methods to Sum Columns in Excel

1. Get the Total Sum of a Column with One Click

2. Apply the AutoSum Option to Sum Columns in Excel

3. Calculate Total by Entering the Sum Function Manually in Excel Column

(Video) How to Sum Cells with Text and Numbers in Excel

4. Transform Your Data into Excel Table to Sum Columns

5. Add Up a Column in Excel Based on Criteria

6. Calculate Subtotal for Filtered Cells in a Column in Excel

7. Find Out the Running Total of an Excel Column

How to Sum Columns in Excel: Knowledge Hub

Conclusion

Related Articles

Sum Columns in Excel.xlsx

(Video) How to Sum by Group in Excel

## 7 Methods to Sum Columns in Excel

To show 7 methods to get the total of a column in Excel, we’ll use the following dataset. It represents an order summary for 6 products for an online shop. The whole year is divided into parts like Jan-Apr, May-Aug, and Sep-Dec to show details of the order’s pattern throughout the year.

### 1. Get the Total Sum of a Column with One Click

This is a quick solution to get the total of a column in Excel without storing the data in a cell. Sometimes it needs to get the total of the whole column or only some selected cells of a column.

• Select a Whole Column: We can select all the cells of a column by clicking the letter of the column. The Excel status bar will show the sum along with the cells count. Here, we selected the E column to get the sum in the status bar.

• Selected Cells of a Column: It only requires selecting cells having our desired data and the status bar will show the value of the summed data.

Here we selected 3 cells (E8:E11) to get the total for pencils order during Sep-Dec.

Read More: How to Sum Selected Cells in Excel (4 Easy Methods)

### 2. Apply the AutoSum Option to Sum Columns in Excel

Excel’s AutoSum function is a frequently used way to get the column totaled. Follow the simple steps:

• We’ll first select the empty cell immediately below the cells that we need to add up. In the Home tab, click the AutoSum option from the Editing group.

• Excel will add the SUM function and range of cells in the selected cell.

• Now, we need to press Enter key to get and store the column total in cell C11.

Read More: How to Sum Range of Cells in Row Using Excel VBA (6 Easy Methods)

### 3. Calculate Total by Entering the Sum Function Manually in Excel Column

We can put the SUM Function to calculate the sum. A column can be partially or fully selected to perform the calculation. It is one of the most widely used methods in Excel. Let’s see that with an example:

• At first, select the cell where we want to see the summed value and write the SUM function.

(Video) How to Count Matches in Two Columns in Excel

• After that, select the cell range. As we want to know the sum of orders for Jan-Apr, we selected C5:C10.

• Finally, hit the Enter key to get the result.

### 4. Transform Your Data into Excel Table to Sum Columns

This method works fine for a dataset having a lot of columns. Transforming the dataset into an Excel table gives an easy way to calculate the total for the columns. Let’s do an example:

• In the 1st step, select the dataset. Then, from the Insert tab choose the Table button to click.

• Click OK on the Create Table window. This will turn the dataset into an Excel table.

• Go to the Design tab and check the Total Row box.

• As an output, we can see the sum of the orders for the Sep-Dec column in the E11 cell.

• To get the totals for other columns (Jan-Apr and May-Aug), get the Autofill Handler and drag it to the left.

• Now, we have all three totals as a final output.

Read More: All the Easy Ways to Add up (Sum) a column in Excel

• Use SUM Function in Excel (With 6 Easy Examples)
• SUMIF Function across Multiple Columns in Excel (4 Methods)
• How to Sum Rows in Excel (9 Easy Methods)
• Use SUMPRODUCT Function in Excel (4 Examples)
• SUMIF Function across Multiple Columns in Excel (4 Methods)

### 5. Add Up a Column in Excel Based on Criteria

Sometimes we need to find out the total of a column based on required criteria. To achieve this, we have to use the SUMIF function. In this example, we took the sum for the three-time duration but only for orders greater than 100. Let’s explore further:

• At first, select the cell that will hold the summed value. Then write the formula into that.

`=SUMIF(C5:C10,">100",C5:C10)`

And hit Enter.

(Video) How to Copy a Formula with Changing Cell References in Excel

• The above formula results in 560 which is the sum of 125, 325, and 110.

• By using the Autofill Handler we can also calculate the totaled columns for the other two-time durations based on the criteria.

Read More: How to Add the Sum of a Column in Excel (7 Easy Ways)

### 6. Calculate Subtotal for Filtered Cells in a Column in Excel

To get the total for only the visible data, we can use the filter feature of Excel. This will allow us to show the filtered result required for data analysis of a big set of data. Here is an example:

• First, we’ll select the columns that are needed to be filtered. Then from the Data tab select the Filter option.

• This will show a filter option with an arrow like this.

• Clicking this arrow will open up a window from where we’ll check the option for pens only (Black Pen, Red Pen, and Blue Pen).

• Now, our dataset is filtered only for pen items.

• In this step, we used AutoSum to calculate totals for the columns.

Read More: How to Sum Filtered Cells in Excel (5 Suitable Ways)

### 7. Find Out the Running Total of an Excel Column

This method will show how to calculate the running total of a column in Excel using the SUM function. Let’s dive in:

• In the first cell of the Running Column of Jan-Apr column, put the following formula

`=SUM(C\$5:C5)`

• Get the Autofill Handler and drag it down to the bottom of the column.

(Video) How to Convert Number to Words

• Output is the running total for the Jan-Apr column.

Read More: How to Sum Only Visible Cells in Excel (4 Quick Ways)

## How to Sum Columns in Excel: Knowledge Hub

• Add up Columns in Excel
• Add Two Columns in Excel
• Sum Every Nth Column
• Sum Columns by Color
• Sum Columns when Filtered
• Sum Columns in Excel Table
• Total a Column in Excel
• Excel Sum Entire Column

## Conclusion

Finally, we reach the end of the article and got familiar with different ways to calculate a sum of a column in Excel. Any questions or suggestions, don’t forget to put them in the comment box below.

## Related Articles

• Use SUMIF Function in Excel (With 5 Easy Examples)
• [Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)
• How to Sum by Group in Excel (4 Methods)
• Shortcut for Sum in Excel (2 Quick Tricks)
• How to Sum Multiple Rows in Excel (4 Quick Ways)
• Sum Cells in Excel: Continuous, Random, With Criteria, etc.
• How to Sum Multiple Rows and Columns in Excel

## FAQs

### How to do full column sum formula in Excel? ›

If you need to sum a column or row of numbers, let Excel do the math for you. Select a cell next to the numbers you want to sum, click AutoSum on the Home tab, press Enter, and you're done. When you click AutoSum, Excel automatically enters a formula (that uses the SUM function) to sum the numbers.

How do I sum multiple columns in Excel with criteria? ›

To sum cells that match multiple criteria, you normally use the SUMIFS function. The problem is that, just like its single-criterion counterpart, SUMIFS doesn't support a multi-column sum range. To overcome this, we write a few SUMIFS, one per each column in the sum range: SUM(SUMIFS(…), SUMIFS(…), SUMIFS(…))

How do I sum every 10 cells in Excel? ›

How to sum every n rows in Excel
1. Enter SUMN_ROWS(C3:C14, 4,2) function.
2. Select the range: C3:C14.
3. Add how many cells you want to summarize (4)
4. Set which group of n rows will calculate (2)
5. The function returns the sum of every n rows.
Feb 12, 2023

How do I sum 6 cells in Excel? ›

Navigate to the Home tab -> Editing group and click on the AutoSum button. You will see Excel automatically add the =SUM function and pick the range with your numbers. Just press Enter on your keyboard to see the column totaled in Excel.

How do I sum only columns in Excel? ›

Just organize your data in table (Ctrl + T) or filter the data the way you want by clicking the Filter button. After that, select the cell immediately below the column you want to total, and click the AutoSum button on the ribbon. A SUBTOTAL formula will be inserted, summing only the visible cells in the column.

How do I sum text columns in Excel? ›

If you are looking for an Excel formula to find cells containing specific text and sum the corresponding values in another column, use the SUMIF function. Where A2:A10 are the text values to check and B2:B10 are the numbers to sum. To sum with multiple criteria, use the SUMIFS function.

How do you create a formula to sum all of the above cells in Excel? ›

Other formulas for tables
1. Click the table cell where you want your result.
2. On the Layout tab next to the Table Design tab, select Formula.
3. Check between the parentheses to make sure Word includes the cells you want in the sum. =SUM(ABOVE) adds the numbers in the column above the cell you're in.

How do I sum multiple columns at once? ›

Press "Ctrl + Space" to select it, then hold "Shift" and using the lateral arrow keys to select the other columns. After selecting all the columns you want to add together, the bar should display a formula such as "=SUM(A:C)," with the range displaying the column letter names.

What is the Sumifs function in Excel? ›

The SUMIFS function, one of the math and trig functions, adds all of its arguments that meet multiple criteria. For example, you would use SUMIFS to sum the number of retailers in the country who (1) reside in a single zip code and (2) whose profits exceed a specific dollar value. Play.

What is the easiest way to sum in Excel? ›

One quick and easy way to add values in Excel is to use AutoSum. Just select an empty cell directly below a column of data. Then on the Formula tab, click AutoSum > Sum. Excel will automatically sense the range to be summed.

### What is the easiest way in using the sum function? ›

Things to remember about the SUM Function

The easiest way to apply the function is to simply select a cell next to the numbers that we wish to add and click on AutoSum on the Home tab. We can then press the Enter key and the SUM formula is automatically inserted.

How do I automatically sum cells in Excel? ›

How to AutoSum in Excel
1. Select a cell next to the numbers you want to sum: To sum a column, select the cell immediately below the last value in the column. ...
2. Click the AutoSum button on either the Home or Formulas tab. ...
3. Press the Enter key to complete the formula.
Mar 21, 2023

How do I sum cells on every sheet in Excel? ›

Sum Across Multiple Sheets in Excel – Range of Cells
1. Select the first worksheet (Q1 in this example)
2. Hold the SHIFT key and click on the last worksheet tab name (Q4 in this example)
3. Now in the active sheet, select B2:D2.
4. Hit the Enter key.

How do I sum all cells with the same text in Excel? ›

First, we will select the cell range containing the text we want to add. Suppose in this case the cell range is A2 to A4. 2. Then we will apply the formula in the formula box =SUMIF(A1:A4,”Fruit”,C1:C4).

How do I sum specific cells in a table? ›

Click the table cell where you want your result to appear. On the Layout tab (under Table Tools), click Formula. In the Formula box, check the text between the parentheses to make sure Word includes the cells you want to sum, and click OK. =SUM(ABOVE) adds the numbers in the column above the cell you're in.

Can you sum every other column in Excel? ›

You can use SUMPRODUCT, MOD and COLUMN functions to filter every nth column and sum the values in those columns.

How to apply sum formula to entire column in Excel without dragging? ›

Click Home > Fill, and choose either Down, Right, Up, or Left. Keyboard shortcut: You can also press Ctrl+D to fill the formula down in a column, or Ctrl+R to fill the formula to the right in a row.

How do I sum an entire column in sheets? ›

Note: This feature doesn't work for some numbers or currency formats.
2. Highlight the cells you want to calculate.
3. In the bottom right, find Explore. . Next to Explore, you'll see "Sum: total."
4. To see more calculations, click Sum. Average. Minimum. Maximum. Count.

How do I sum multiple cells with multiple criteria in Excel? ›

=SUMIFS(D2:D11,

In other words, you want the formula to sum numbers in that column if they meet the conditions. That cell range is the first argument in this formula—the first piece of data that the function requires as input.

What is the difference between sum and Sumifs in Excel? ›

You know SUM function has the capability to add items, and SUMIF function extends the capabilities of SUM function by giving you the choice to SUM only those items that meet a particular criterion.

### Is there a difference between Sumif and Sumifs? ›

The only difference between Excel SUMIFS & SUMIF functions is that SUMIFs can check for multiple criteria at once, while SUMIF can check for one criterion at a time.

What is the difference between Sumif and Sumifs formula? ›

SUMIF can evaluate just one condition at a time while SUMIFS can check for multiple criteria. Syntax. With SUMIF, the sum_range is the last and optional argument - if not defined, the values in the range argument are summed. With SUMIFS, sum_range is the first and required argument.

How do I sum the number of cells with a specific text in sheets? ›

Google Sheets Sum If Cell Contains Text
1. In an empty cell, type the SUMIF formula. ...
2. Select the range to which you want to apply the criterion. ...
3. Add the criterion you want to apply to the selected range. ...
4. Next, select the range with the values you want to sum if it's different from the first range selected. ...
5. That's it.
Dec 19, 2022

Which function is used to calculate sum of multiple cells? ›

The SUM function adds values. You can add individual values, cell references or ranges or a mix of all three.

Why won't Excel do a sum? ›

The most common reason for an Excel formula not calculating is that you have inadvertently activated the Show Formulas mode in a worksheet. To get the formula to display the calculated result, just turn off the Show Formulas mode by doing one of the following: Pressing the Ctrl + ` shortcut, or.

How do you write a formula for a sum function? ›

Select the cell where you want to add your formula (in our example, this is B15). Type =SUM(B3:B7). Notice how Excel highlights the range of cells that will be added. Hit enter.

How do I calculate amounts in Excel? ›

For simple formulas, simply type the equal sign followed by the numeric values that you want to calculate and the math operators that you want to use — the plus sign (+) to add, the minus sign (-) to subtract, the asterisk (*) to multiply, and the forward slash (/) to divide.

How to do quick analysis in Excel? ›

Quick Analysis takes a range of data and helps you pick the perfect chart with just a few commands. Select a range of cells. Select the Quick Analysis button that appears at the bottom right corner of the selected data. Or, press Ctrl + Q.

How do I sum only certain cells in Excel? ›

For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John." To sum cells based on multiple criteria, see SUMIFS function.

How do you add 5 cells in Excel? ›

Select the cell, or the range of cells, to the right or above where you want to insert additional cells. Tip: Select the same number of cells as you want to insert. For example, to insert five blank cells, select five cells. Hold down CONTROL, click the selected cells, then on the pop-up menu, click Insert.

### How do I sum the last 7 rows in Excel? ›

=SUM(OFFSET(D11, COUNTA(D11:D100)-7,0,7,1)) As we can see the Sum of the last 7 transactions from our example is \$28,472. This completes our tutorial on Excel sum last 7 values in row!

How do I sum specific columns? ›

Press "Ctrl + Space" to select it, then hold "Shift" and using the lateral arrow keys to select the other columns. After selecting all the columns you want to add together, the bar should display a formula such as "=SUM(A:C)," with the range displaying the column letter names.

Why is sum not working for some cells in Excel? ›

Check if your workbook is in manual calculation mode. Go to tab "Formulas" on the ribbon, then press with left mouse button on the "Calculations Options" button. In this case, the setting was on "Manual", changing it back to "Automatic" makes the SUM formula work as intended again.

How do I sum a column but exclude certain cells? ›

Re: How to exclude cells in a column from Sum function in excel (using Ctrl)?
1. Select the range you want to sum, using Shift and/or Ctrl as needed.
2. Click in the name box on the left hand side of the formula bar.
3. Enter a name for the selected range (must be one word, underscores allowed), for example MyRange.
4. Press Enter.
Oct 30, 2021

How do I SUM a certain number of rows? ›

To total the values in a range containing several rows, simply specify the desired range in the Sum formula. For example: =SUM(B2:D6) - sums values in rows 2 to 6.

How do you sum all cells above in Excel? ›

=SUM(ABOVE) adds the numbers in the column above the cell you're in. =SUM(LEFT) adds the numbers in the row to the left of the cell you're in. =SUM(BELOW) adds the numbers in the column below the cell you're in. =SUM(RIGHT) adds the numbers in the row to the right of the cell you're in.

How do I sum multiple text in Excel? ›

Sum if cell contains text

If you are looking for an Excel formula to find cells containing specific text and sum the corresponding values in another column, use the SUMIF function. Where A2:A10 are the text values to check and B2:B10 are the numbers to sum. To sum with multiple criteria, use the SUMIFS function.

## Videos

1. How to Change Cell Color Based on Date Using Excel Formula
(ExcelDemy)
2. How to Auto Generate Number Sequence in Excel
(ExcelDemy)
3. How to Match Multiple Criteria from Different Arrays in Excel
(ExcelDemy)
4. How to Copy and Paste Without Changing the Format in Excel
(ExcelDemy)
5. How to Use TEXT Function to Format Codes in Excel
(ExcelDemy)
6. How to Calculate Cumulative Percentage in Excel
(ExcelDemy)

## References

Top Articles
Latest Posts
Article information

Author: Clemencia Bogisich Ret

Last Updated: 09/20/2023

Views: 6301

Rating: 5 / 5 (80 voted)

Author information

Name: Clemencia Bogisich Ret

Birthday: 2001-07-17

Address: Suite 794 53887 Geri Spring, West Cristentown, KY 54855

Phone: +5934435460663

Job: Central Hospitality Director

Hobby: Yoga, Electronics, Rafting, Lockpicking, Inline skating, Puzzles, scrapbook

Introduction: My name is Clemencia Bogisich Ret, I am a super, outstanding, graceful, friendly, vast, comfortable, agreeable person who loves writing and wants to share my knowledge and understanding with you.