INTRODUCTION
SUM function comes under the MATH AND TRIG FUNCTIONS category in Excel.
It is again one of the frequently used functions in Excel. This function totals the different values given explicitly or in cells.
Sum can be done using the formula or the function.
PURPOSE OF SUM IN EXCEL
IF FUNCTION CHECKS THE GIVEN CONDITION AND SETS THE VALUE AS PER THE OUTCOME OF CONDITION.(TRUE OR FALSE)
PREREQUISITES TO LEARN SUM
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Basic understanding of how to use a formula or function.
- Of course, Excel software.
Helpful links for the prerequisites mentioned above What Excel does? How to use formula in Excel?
SYNTAX: SUM FUNCTION
The Syntax for the function is
SYNTAX FOR SUM USING SEPARATE VALUES
=SUM(number1,number2, …)
NUMBER 1 , NUMBER 2 are the different values/cell address which we want to sum.
*The upper number of arguments is 255 i.e. 255 values can be added in this function separately.For ranges, other notation is used.
SYNTAX FOR SUM USING RANGE
=SUM(STARTING_CELL_ADDRESS:ENDING_CELL_ADDRESS)
EXAMPLE:SUM FUNCTION IN EXCEL
DATA SAMPLE
Two numbers are given in cell G8 and G9.Let us find out the sum in cell G10.
STEPS TO USE SUM
The example contains the values as 12 and 16 in the cell G8 AND G9.
We want the result in G10. So we have written the formula in the cell as
=SUM(G8,G9)
We get the result as 28.
DIFFERENT WAYS OF USING SUM FUNCTION
SUM USING TEXT VALUES
A case may occur when we have both the values stored as TEXT.
Suppose we have values 5 and 6 stored as text.
In that case we can sum them up as
=SUM(“5″,”6”)
The result will apppear as 11.
Actually the SUM FUNCTION first converts the text into number and then calculates the result.
SUM USING BINARY VALUES
SUM functions also goes easy with the binary values i.e. TRUE AND FALSE.SUM function treats TRUE as 1 and FALSE as 0These words can be used as such in the SUM FUNCTION.E.G.=SUM(TRUE, FALSE,TRUE) will result in 2.
SUM USING A RANGE
THIS PARTICULAR USAGE IS GOING TO BE VERY FREQUENT IN PRACTICAL CASES.
Suppose , we have a 1000 numbers to be totaled. Its obvious that its not easy or possible to use the function as SUM(NUMBER 1, NUMBER 2…..NUMBER 1000) as only 255 numbers are taken and its not easy or almost impossible to write 1000 numbers in the formula.
So for such cases, we make use of ranges.
Range (Click here for more info about range operator) is marked as A1:A1000 which means all cells from A1 TO A1000.
So we can solve this problem by putting the formula as
=sum(A1:A1000)
It’ll calculate the sum of all numbers written in the cells from 1 to 1000.
EXAMPLE-SUM USING ALL KIND OF DATA FORMATS
The example shows the different ways by which we can pass the parameters in the SUM FUNCTION.
The formula used
=SUM(F7:F10,”45″,TRUE,FALSE,12)
contain the RANGE, TEXT VALUE, LOGICAL BINARY VALUES and DIRECT NUMERICAL value.
The result is 80 as
F7 TO F10=22+
“45”=45+
TRUE=1+
FALSE=0+
12=12
=80
KNOWLEDGE BYTES
NESTED IF
In many situations we’ll find a case where we will be having more than one conditions.In that case , we can make use of NESTED IF .The notation goes like this. =IF(condition1, value1,(if(condition 2,value 2, value 3))if condition 1 is true, value 1 will go otherwise control will go to next condition,if condition 2 is true, value 2 will come else value 3.++