## How one can make use of the SUM Function in Microsoft ExcelIt was well known that the respective
The respective that can be used for the purpose of " FORMULA:
The numbers like 'num1', 'num2', and 'num_n 'basically denote the numeric values or the numbers which we wish to add on. And it can also accept numbers up to 255 or individual arguments in a single formula. ## How to Sum in Microsoft Excel by making use of the simple arithmetic calculation?We can make use of the Microsoft Excel as a mini calculator if we need a quick total of the individual cells, and need to utilize the plus sign operator (+) as in a regular arithmetic operation of the addition. **For example: 1) Adding of values none other than: 1,2,3****2) Adding of cell: B1, C1, D1**
Moreover if we want to sum a few hundred rows, then in that scenario, referencing each cell in a formula does not sound like a good idea. ## How we can make use of the SUM function in Microsoft ExcelThe Microsoft Excel SUM function is primarily mathematics as well as the trigonometric function which are responsible for adding up the values. And in our Excel SUM formula, every argument can be: positive value as well as the negative value, or it could be in the form of the range respectively. **For example:**
The Excel SUM function is very much helpful when we are required to add on the values from the different ranges, or we can easily combine numeric values, cell references, and ranges as per our requirement. **For example:**
The below screenshot shows these and a few more SUM formula examples respectively: And in the real-life sheet Microsoft Excel " **For example:**In this one can easily embed out the SUM in theargument of the**value_if_true****IF function**for the purpose of adding out the numbers in columns B, C, and D if all three cells in the same row contain values, as well as show a warning message if any of the cells are blank respectively:
## How one cans easily AutoSum in Microsoft Excel?If in case we are required to sum one range of the numbers, whether it could be a column, row then we can let Microsoft Excel to write an appropriate SUM formula for us efficiently. And for this, we will select a cell next to the numbers which we want to add; after that, we will click on the group, we will press the EditingEnter key from our keyboard.It is clearly visible from the above screenshot that the Microsoft Excel's AutoSum feature will not only enters a And apart from calculating the total, we can make use of the ## How we can sum a column in Microsoft Excel?Now to sum numbers in a given specific column, we can use either the **For example:**If we want to sum values in column B, that is from cell B2 to B8, we are required to enter the following Excel SUM formula as well:
Let us assume that, we are having a column which we want to sum and it has a variable number of rows, which means that the new cell can be easily added while the existing ones can be deleted at any time. So in that particular case, we can easily sum out the entire column by just supplying a reference without specifying a lower or upper bound. **For example =SUM (B: B)**
And we all knew that, supplying a column reference to the respective Microsoft Excel Sum formula will totals out the entire column while just ignoring the header. But sometimes, the column header which we want to total can have a number. Or, we could exclude the first few rows with the respective numbers irrelevant to the data we want to sum out. Furthermore, the respective Microsoft Excel will not accept a mixed SUM formula with an explicit lower bound but an accept without an upper bound, and now, if we want to exclude the first few rows from summation, then we can make use of the following workarounds as well. - Summation of the entire column and after then will subtract out the cells which we do not want to include in the total.
Moreover, it should be remembered that the respective worksheet size should have the limits, and we can easily specify the upper bound of our Excel SUM formula that are based upon the maximum number of rows used in our Microsoft Excel version. **For example:**To sum column B without the header (i.e., excluding cell B1), we can make use of the following formulas as well:
- In Microsoft Excel version 2007, Microsoft Excel version 2010, Excel 2013, and Excel 2016:
**=SUM (B2:B1048576)** - And in Microsoft Excel version 2003 and lower:
**=SUM (B2:B655366)**
## How can one Sum rows in Microsoft Excel?Similar to the totaling of a column, we can quickly sum out a row in Microsoft Excel by using the **For example:**For the purpose of adding out the values in the respective cells that are B2 to D2, we can make use of the following formula as well:
Now to add values in each row individually, we can drag down our Sum formula as well. And the main point which needs to be remembered is to make use of the relative references (without $) or mixed cell references (where the $ sign fixes only the columns). **For example:**
And to total out the values in a given range that are containing several rows, we are required to specify out the desired range in the Sum formula respectively: ## How can the Microsoft Excel Total Row sum data in a table?Let us now assume that, our respective data is organized in an Excel table. In that case, we can benefit from the Furthermore, one of the most significant advantages of making use of the Microsoft Excel tables is that they auto-expand new rows, so any new data we input in a table will be get automatically added in our formulas as well. And if we want to convert a normal range of the cells into a given table, we can select it and will then press out the shortcut Ctrl + T shortcut (or click Table on the
After the arrangement of the data in a selected table, we can easily insert a total row in this way as well: - Firstly we will be clicking anywhere in the Table to display the
with the**Table Tools****Design**tab. - And on the
tab, which is present under**Design**group, we will be selecting the**the Table Style Options****Total Row**box:
And the other way can be used to add a total row in Microsoft Excel is to right-click any cell within the given Table, and then will be clicking on the
It was well knew, that the respective total row get appears just at the end of the Table, and Microsoft Excel does its best for the purpose of determining out how we would like to calculate data in the selected Table as well. As in our sample table, the values which are present in the column D (rightmost column) will be get added automatically, and the Sum will be displayed in the Total Row: And to total the values in other columns, we are required to select a corresponding cell in the total row; after that, we will be clicking on the dropdown list arrow and then will select the If we want to perform another calculation, we will select the corresponding function from the dropdown list, like And if in case the total row get automatically displays out a total for a respective column that does not need one, just open the dropdown list and will select the ## How can one easily sum only filtered (visible) cells in Microsoft ExcelSome of the time we are required to filter or hide some out some of the data in our separate worksheets for more effective data analysis. A usual Sum formula would not work in this case; this is because of the reason that the And if in case want to sum only and only the visible cells in a filtered list, then the fastest way is to just organize our data in Microsoft Excel table and then turn on the Besides this, the other way which can be used for the The SUBTOTAL function has the following syntax:
In which, **Function_num:**It is a number which are ranging from 1 to 11 or 101 to 111, and it will specify out the function which can be used to the subtotal as well.
Here in this, we are quite interested only in the And if in case we want to only sum visible cells, irrespective of how exactly the irrelevant rows were hidden, in that case, we can make efficiently make use of the number **Ref1, Ref2 ...:**It is termed to be the cells or ranges which we want to subtotal, and the very first Ref argument is required.
## How does one efficiently perform running total (cumulative Sum) in Microsoft Excel?Now for the purpose of calculating a running total in Microsoft Excel, we can write a usual SUM formula with the clever use of the two references that are none other than **For example:**To display out the cumulative Sum of the respective selected numbers in column B, we will be entering the following formula in cell C2 and then will copy it down to other cells as well:**=SUM ($B$2:B2)**
And the relative reference B2 will be getting changed automatically that are based upon the relative position of the row in which the formula is copied efficiently: ## How can one sum across sheets in Microsoft Excel?Now in this, if we are having a several worksheets and that with the same layout, the same data type, in that scenario, we can easily add out the values in the given same cell or in the same range of cells in the different sheets with the use of the single SUM formula respectively. And a so-called 3-D reference is what does the trick:
Or
Now the first formula will usually add on the values in the selected cell that is ## Microsoft Excel conditional sumIf in case, our respective task requires adding of only those cells that will meet a particular condition or a few conditions, then in that case we can effectively make use of the **For example:**The following SUMIF formula will eventually add only those amounts in column B in which we have**"**status in column C:*Completed*"**=SUMIF(C: C,"completed", B:B )**
And in order to calculate out a conditional sum with the multiple criteria, we can just make use of the
Are we trying to add a few values or total a column in our Microsoft Excel sheet, but a simple SUM formula only computes some of it? And if in case the Microsoft Excel SUM function is not working properly, then it is most likely because of the following reasons which are mentioned below in details.
It is one of the most accessible errors to fix. As in 99, 100 cases, the
And the other common reason for a Sum formula (or Excel AutoSum) not working is that the numbers are formatted in the form of text values. At first sight, they will look like a normal numbers, but Microsoft Excel will perceives them as text strings and leaves them out of calculations.
Apart from the numbers that are basically formatted in the form of the text, a
If, against all expectations, our respective Sum formula returns a more significant number than it should be remembered that the
And when a , and then will be clicking on the Calculate OptionsAutomatic option respectively.Next TopicIncome Tax Calculator in Microsoft Excel |