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.
Table of Contents
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.
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).
As quickly as you choose the whole column, you’ll discover that the standing bar reveals you the SUM of the column.
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 dont 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 sumClick on the Method tabWithin the Operate Library group, click on on the Autosum choice
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.
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 dont 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:
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(Choose 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).Hit the Enter key.
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.
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)
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:
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 cant use the SUM operate. On this case, it is advisable to use the AGGREGATE or SUBTOTAL operate.
For those whore 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 youre 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)
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 youre 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, its another reason to make use of it.
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 on on the Desk 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.Click 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.Within the Desk Type Choices group, verify the Whole Row choice
The above steps would immediately add a totals row on the backside of the desk and provides the sum of all of the 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.
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)
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)
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.
