SUM Function / Formula in Excel
In the SUM function, we can add values, can add individual values, cell references or ranges, or all three.
For
example
=SUM (D2: D12) Adds the values in cells D2 to D12.
=SUM(D2, D6, D5, D7, D8, D10, D11) will Add the values in selected cells against the years 2016 and 2017 only as shown in the figure below. We can add any value from any
cell just give the reference of the cell. For example =SUM(D2,F4,G6,H9 and so
on
SUMIF()
Function/Formula.
We use the SUMIF formula to
sum the values in a range that meets specific criteria. Suppose that column B has Sales values yearly, the Formula for SUMIF is as follows
SUMIF(Range, Criteria, Sum-Range)
There are three arguments in the SUMIF formula.
The first argument is Range
The range of cells that we want to set as
criteria. Cells in each range must be numbers, names, and references that contain numbers. Blank and text values will be
ignored.
Criteria:-
The criteria in the form of
a number, expression, cell reference, or text, For example, criteria can be
expressed as any number, number with
<,> or = with double
quotes “” for example “>2019”
“<2019 =>2019 or any text with, "Ali", or date() or function of date for example today()
Sum-range:-
This argument is Optional. The actual cells
to add, if we want to add cells other than those specified in the range argument.
If the (sum range) argument is left, then Excel adds
the cells that are specified in the range argument.
In the following table, we want to sum year-wise
sales that are in different columns First we Will calculate sales for the year
2016. Here range will be column A which is Year. The criteria are 2016 and the Sum range
is column B which is Sales.
=SUMIF(A2:A12,"=2016",B2:B12)
Similarly we can use criteria such as ">2016" or "<2018"
The formula will be like this
=SUMIF(A2:A12,">2016",B2:B12)
this will calculate sales against the year greater than 2016.
=SUMIF(A2:A12,"<2018",B2:B12)
and similarly, this will calculate sales against years less than 2018
=SUMIFS()
Function / Formula.
SUMIFS function is used to sum cells
that meet multiple criteria.
Criteria may be numbers logical
operators <, >, =, Dates, and Names.
Formula
=SUMIFS(sum-range, criteria-range1, criteria1, criteria-range2,
criteria2, and so on ...)
First of all select range to be
added then select the range for criteria.
For example, we want to add Sales of TV in January. The column we want to add is sales i.e. column D. It will be our add range. First criteria-range
that is Items and Criteria is =Tv, The Second Criteria-range that is Month
column B, which is =Jan.
Now formula will be
=SUMIFS(D2:D12, E2:E12,"=Tv",B2:B12,"=Jan")
The result of Formula will be as below.
Now try this formula for three criteria that are
“Sales of Tv by Imran in Jan”
In this formula, we will add Sales for TV by Imran in the Month of January. The
formula will be like this.
=SUMIFS(D2:D12,
E2:E12,"=Tv",B2:B12,"=Jan",C2:C12,"=Imran")
Use it now.
