Advance Excel Learning - SUMIF Formulla - Software Applications - MS Excel - TIK Share

Advance Excel Learning - SUMIF Formulla


CA Pulkit Sharma

2013-04-10

=SUMIF(range,criteria,sum_range)

=SUMIF(range containing comparable data,Cell to extract data,Range containing Amounts/Quanities)

 

SUMIF is Formulla in Excel for DATA Analysis.SUMIF used  along with IF formulla can provide the results beyond your imagination.

 

SUMIF formulla is mostly used to find the Total of a selected Object out of given Objects; Confused or Bounced well lets do some practical.

 

Suppose your company has announced a scheme that who buys for more than 250 will be given a Gift, below is an example of just 5 buyers but if you have lakhs of buyers it really beocomes difficult to find out total quantities sold to each Buyer. So we will use SUMIF formulla here.

 

Buyers(A8:A25)

Quantity(B8:B25)

Amar

23

Akbar

3435

Anthony

46

Ram

565

Shyam

466

Geeta

57

Sheeta

67

Anthony

6

Ram

876

Shyam

98

Geeta

79

Sheeta

9

Amar

98

Akbar

0

Anthony

435

Ram

54

Shyam

543

Geeta

53

 

Buyer Name(A28)

Total Quantity Sold

Total Quantity Sold

Anthony

487

=SUMIF(A8:A25,A28,B8:B25)

 

You can see that using the formulla you have to just replace the name of Buyer in column A30, and you will get the total quantities sold to that Buyer.

Well this was just a simple example, now we will try to combine the SUMIF formulla with IF formulla.

In above example we only get the total quantities, suppose your boss want only whether Gift is to be given or not. Than we can use IF formulla to fulfill this requirement.

 

Buyer Name

Total Quantity Sold

Total Quantity Sold

Anthony

Give Gift

=IF(SUMIF(A8:A35,A35,B8:B25)>250,"Give Gift","Don’t Give")

 

So you can observe that by combining the SUMIF formulla with IF formulla we got the required results.

 

 

Exercise

If you understood it than try to use it, and find

        How much Shyam has bought and Does he deserve a Gift.

 

Download file for offline reading and excercise  sumif-formula.zip 

sunita thapa

2014-05-07

thanks pulkit for sharing this is very useful for all

Reply

CA Pulkit Sharma

2014-05-07

Thank you Sunita.
You can also help other by sharing your knowledge with others on this site.
To thank me, please start a discussion :)

Reply