“SUMIF”
Used for – “SUMIF” function in MS Excel is
used to find the sum of values in a range that meet criteria that you have
specified.
Syntax – SUMIF(range, criteria,
[sum_range])
range – It is a array within which you want to give the criteria. In this range each cell must contain a number, alphabets or alphanumeric values which can be used as criteria. It is a compulsory field and if you did not fill any value excel will not proceed further.
range – It is a array within which you want to give the criteria. In this range each cell must contain a number, alphabets or alphanumeric values which can be used as criteria. It is a compulsory field and if you did not fill any value excel will not proceed further.
criteria – As the name indicates it is a
cell or value or condition or expression or a function/formula that defines
which cells to be added. It is a compulsory field and if you did not fill any
value excel will not proceed further.
[sum_range] – As the name indicate it
is a range which contains number whose cells are being sumup based on the given
criteria. This is also an optional field and is an array. If you donot specify
the sum_range excel will sum up the cell which are in range.
Example – Following example will clear
the SUMIF function:-
In below table column A, B, & C
contains some sales data for particular item in specified quarter. Now column F
contains the SUMIF function with different criteria.
A
|
B
|
C
|
D
|
E
|
F
|
G
|
H
|
I
|
|
1
|
Quater
|
ITEM
|
Sale
|
Total Sale
|
|||||
2
|
1
|
Orange
|
5000
|
Orange
|
=SUMIF(B2:B7,E2,C2:C7)
|
||||
3
|
1
|
Banana
|
2000
|
Banana
|
=SUMIF(B2:B7,E3,C2:C7)
|
||||
4
|
2
|
Papaya
|
1000
|
=SUMIF(C2:C7,">2000")
|
|||||
5
|
2
|
Orange
|
3000
|
=SUMIF(B2:B7,"*a",C2:C7)
|
|||||
6
|
3
|
Banana
|
4000
|
||||||
7
|
3
|
Papaya
|
2000
|
Cell
F2 & F3 contains the total sale of orange & banana for all the three
quarter respectively, while the cell F4 contains the total sale which is
greater than 2000 and cell F5 coatains the total of all the items which end
with “a”.
And
result you get as under:-
A
|
B
|
C
|
D
|
E
|
F
|
G
|
H
|
I
|
|
1
|
Quater
|
ITEM
|
Sale
|
Total Sale
|
|||||
2
|
1
|
Orange
|
5000
|
Orange
|
8000
|
||||
3
|
1
|
Banana
|
2000
|
Banana
|
6000
|
||||
4
|
2
|
Papaya
|
1000
|
12000
|
|||||
5
|
2
|
Orange
|
3000
|
9000
|
|||||
6
|
3
|
Banana
|
4000
|
||||||
7
|
3
|
Papaya
|
2000
|
NOTE
:- Please
do care of following points when you use SUMIF :-
·
SUMIF has the limitation that you can match only 255 characters if
you match more than it result incorrect.
·
Range and sum_range may or may not be of equal size. If they are
of unequal size then it sum the sum range based upon upper left most cell
specify by you in both and it will take longer time for calculation than
expected.
·
The blanks and numeric values in text format being ignored in this
function.
Dear readers, after reading the Content please ask for advice and to provide constructive feedback Please Write Relevant Comment with Polite Language.Your comments inspired me to continue blogging. Your opinion much more valuable to me. Thank you.