To get the sum of a complete column is one thing most of us need to do very often. For instance, in case you have the gross sales information to this point, you might need to rapidly know the whole sum within the column to the gross sales worth until the current day.

Chances are you’ll need to rapidly see what the whole sum is or you might have considered trying is as a method in a separate cell.

This Excel tutorial will cowl a few fast and quick strategies to sum a column in Excel.

Choose and Get the SUM of the Column in Standing Bar

Excel has a standing bar (on the backside proper of the Excel display screen) which shows some helpful statistics in regards to the chosen information, similar to Common, Rely, and SUM.

Suppose you could have a dataset as proven beneath and also you need to rapidly know the sum of the gross sales for the given weeks.

Weekly Sales Dataset

Weekly Sales Dataset

To do that, choose the whole Column B (you are able to do that by clicking on the B alphabet on the high of the column).

Select the entire column to get the sum of the values

Select the entire column to get the sum of the values

As quickly as you choose the whole column, you’ll discover that the standing bar reveals you the SUM of the column.

Sum value of the selected cells in the satus bar

Sum value of the selected cells in the satus bar

It is a actually fast and straightforward option to get the sum of a complete column.

The benefit of utilizing the standing bar to get the sum of the column is that it ignores the cells which have the textual content and solely considers the numbers. In our instance, cell B1 has the textual content title, which is ignored and the sum of the column is displayed within the standing bar.

In case you need to get the sum of a number of columns, you can also make the choice, and it’ll present you the whole sum worth of all the chosen columns.

In case you don’t need to choose the whole column, you can also make a variety choice, and the standing bar will present the sum of the chosen cells solely.

One destructive of utilizing the standing bar to get the sum is that you would be able to not copy this worth.

If you need, you possibly can customise the standing bar and get extra information similar to the utmost or the minimal worth from the choice. To do that, right-click on the standing bar and make the customizations.

Get the SUM of a Column with AutoSum (with a Single-click/Shortcut)

Autosum is a very superior device that means that you can rapidly get the sum of a complete column with a single click on.

Suppose you could have the dataset as proven beneath and also you need to get the sum of the values in column B.

Beneath are the steps to get the sum of the column:

Choose the cell proper beneath the final cell within the column for which you need the sumSelect the cell where you want the sumSelect the cell where you want the sumClick on the Method tabClick the Formulas tabClick the Formulas tabWithin the Operate Library group, click on on the Autosum choiceClick the Autosum optionClick the Autosum option

Also Read |  Spelling Studying Apps College students

The above steps would immediately provide the sum of the whole column within the chosen cell.

It’s also possible to use the Auto-sum by choosing the column that has the worth and hitting the auto-sum choice within the method tab. As quickly as you do that, it will provide you with the auto-sum within the cell beneath the choice.

Get the Sum using Autosum after selecting the column data

Get the Sum using Autosum after selecting the column data

Notice: Autosum mechanically detects the vary and consists of all of the cells within the SUM method. If you choose the cell which has the sum and take a look at the method in it, you’ll discover that it refers to all of the cells above it within the column.

One minor irritant when utilizing Autosum is that it might not establish the proper vary in case there are any empty cells within the vary or any cell has the textual content worth. Within the case of the empty cell (or textual content worth), the auto-sum vary would begin beneath this cell.

Professional Tip: It’s also possible to use the Autosum function to get the sum of columns in addition to rows. In case your information is in a row, merely choose the cell after the info (in the identical row) and click on on the Autosum key.

AutoSum Keyboard Shortcut

Whereas utilizing the Autosum choice within the Method tab is quick sufficient, you can also make getting the SUM even quicker with a keyboard shortcut.

To make use of the shortcut, choose the cell the place you need the sum of the column and use the beneath shortcut:

ALT = (maintain the ALT key and press the equal to key)

Utilizing the SUM Operate to Manually calculate the Sum

Whereas the auto-sum choice is quick and efficient, in some circumstances, you might need to calculate the sum of columns (or rows) manually.

One purpose for doing this may very well be whenever you don’t need the sum of the whole column, however solely of a number of the cells within the column.

In such a case, you should use the SUM operate and manually specify the vary for which you need the sum.

Suppose you could have a dataset as proven beneath and also you need the sum of the values in column B:

Weekly Sales Dataset

Weekly Sales Dataset

Beneath are the steps to make use of the SUM operate manually:

Choose the cell the place you need to get the sum of the cells/rangeEnter the next: =SUM(Enter the SUM formula manually in a cellEnter the SUM formula manually in a cellChoose the cells that you just need to sum. You should use the mouse or can use the arrow key (with arrow keys, maintain the shift key after which use the arrow keys to pick out vary of cells).Select the range of cells for the SUM formulaSelect the range of cells for the SUM formulaHit the Enter key.SUM formula to sum a column in ExcelSUM formula to sum a column in Excel

The above steps would provide the sum of the chosen cells within the column.

It’s also possible to create and use named ranges within the SUM operate to rapidly get the sum worth. This may very well be helpful when you could have information unfold on a big spreadsheet and also you need to rapidly get the sum of a column or vary. You’d first must create a named vary after which you should use that vary title to get the sum.

Also Read |  How To Make A Film Poster: A Template For College students

Beneath is an instance the place I’ve named the vary – Gross sales. The beneath method additionally offers me the sum of the gross sales column:

=SUM(Gross sales)

Sum a column using named range

Sum a column using named range

Notice that whenever you use the SUM operate to get the sum of a column, it’s going to additionally embody the filtered or hidden cells.

In case you need the hidden cells to not be included when summing a column, it is advisable to use the SUBTOTAL or AGGREGATE operate (coated later on this tutorial).

Sum Solely the Seen Cells in a Column

In case you could have a dataset the place you could have filtered cells or hidden cells, you cannot use the SUM operate.

Beneath is an instance of what can go unsuitable:

SUM Function calculates hidden and filtered cells as well

SUM Function calculates hidden and filtered cells as well

Within the above instance, once I sum the seen cells, it offers me the consequence as 2549, whereas the precise results of the sum of seen cells could be 2190.

The explanation we get the unsuitable result’s that the SUM operate additionally takes the filtered/hidden cells when calculating the sum of a column.

In case you solely need to get the sum of seen cells, you possibly can’t use the SUM operate. On this case, it is advisable to use the AGGREGATE or SUBTOTAL operate.

For those who’re utilizing Excel 2010 or increased variations, you should use the AGGREGATE operate. It does every thing that the SUBTOTAL operate does, and just a little extra. In case you’re utilizing earlier variations, then you should use the SUBTOTAL operate to get the SUM of seen cells solely (i.e., it ignores filtered/hidden cells).

Beneath is the method you should use to get the sum of solely the seen cells in a column:

=AGGREGATE(9,three,B2:B6)

Using Aggregate function to get sum of filtered cells in a column

Using Aggregate function to get sum of filtered cells in a column

The Combination operate takes the next arguments:

function_num: It is a quantity that tells the AGGREGATE operate the calculation that must be achieved. On this instance, I’ve used 9 as I need the sum.choices: On this argument, you possibly can specify what you need to ignore when doing the calculation. On this instance, I’ve used three, which ‘Ignores hidden row, error values, nested SUBTOTAL, and AGGREGATE capabilities’. In brief, it solely makes use of the seen cells for the calculation.array: That is the vary of cells for which you need to get the worth. In our instance, that is B2:B6 (which additionally has some hidden/filtered rows)

In case you’re utilizing Excel 2007 or prior variations, you should use the next Excel method:

=SUBTOTAL(9,B2:B6)

Beneath is a method the place I present the way to sum a column when there are filtered cells (utilizing the SUBTOTAL operate)

Convert Tabular Knowledge to Excel Desk to Get the Sum of Column

Once you convert tabular information to an Excel Desk, it turns into very easy to get the sum of columns.

I at all times suggest changing information into an Excel desk, because it affords quite a lot of advantages. And with new instruments similar to Energy Question, Energy Pivot and Energy BI working so effectively with tables, it’s another reason to make use of it.

Also Read |  The TeachThought Podcast Ep. 173 How Can We Put together Academics For Deeper Studying?

To transform your information into an Excel desk, comply with the beneath steps:

Choose the info that you just need to convert to an Excel TableClick the Insert tabClick the Insert tab in the ribbonClick the Insert tab in the ribbonClick on on the Desk iconClick on the Table iconClick on the Table iconWithin the Create Desk dialog field, be certain that the vary is right. Additionally, verify the choice ‘My desk has headers’ in case you could have headers in your information.Create Table dialog boxCreate Table dialog boxClick on Okay.

The above steps would convert your tabular information into an Excel Desk.

The keyboard shortcut to transform to a desk is Management + T (maintain the management key and press the T key)

Upon getting the desk in place, you possibly can simply get the sum of all of the columns.

Beneath are the steps to get the sum of the columns in an Excel Desk:

Choose any cell within the Excel tableClick the Design tab. It is a contextual tab that solely seems when you choose a cell within the Excel desk.Click the Design tab in Table ToolsClick the Design tab in Table ToolsWithin the ‘Desk Type Choices’ group, verify the ‘Whole Row’ choiceCheck the Total Row optionCheck the Total Row option

The above steps would immediately add a totals row on the backside of the desk and provides the sum of all of the columns.

Using Table to Get the Sum of columns

Using Table to Get the Sum of columns

One other factor to learn about utilizing an Excel desk is that you would be able to simply change the worth from the SUM of the column to Common, Rely, Min/Max, and so on.

To do that, choose a cell within the totals rows and use the drop-down menu to pick out the worth you need.

Change from Sum of column to other values such as Average or Count

Change from Sum of column to other values such as Average or Count

Get the Sum of Column Based mostly on a Standards

All of the strategies coated above would provide the sum of the whole column.

In case you need to solely get the sum of these values that fulfill a criterion, you possibly can simply do this with a SUMIF or SUMIFS method.

For instance, suppose you could have the dataset as proven beneath and also you solely need to get the sum of these values which can be greater than 500.

You possibly can simply do that utilizing the beneath method:

=SUMIF(B2:B11,”>500″,B2:B11)

Using SUMIF to conditionally sum the column

Using SUMIF to conditionally sum the column

With SUMIF method, you should use a numeric situation in addition to textual content situation.

For instance, suppose you could have a dataset as proven beneath and also you need to get the SUM of all of the gross sales achieved by Mark.

On this case, you should use column A as the standards vary and “Mark” as the standards, and the method will provide you with the sum of all of the values for Mark.

The beneath method will provide you with the consequence:

=SUMIF(A2:A11,”Mark”,B2:B10)

Add all sale values in the column for Mark

Add all sale values in the column for Mark

Notice: One other option to get the sum of a column that meets a criterion may very well be to filter the column based mostly on the standards after which use the AGGREGATE or SUBTOTAL method to get the sum of seen cells solely.

You might also like the next Excel tutorials: