Excel: Some Examples with SUMIF Function

Forum to talk about all the latest technologies - ins and outs!!
Post Reply
Admin
Site Admin
Posts: 325
Joined: Tue Sep 25, 2007 12:41 pm

Excel: Some Examples with SUMIF Function

Post by Admin »

SUMIF function is used to sum up values in a given range that meet specified criteria. Its syntax is

SUMIF(range, criteria, [sum_range])


range - range of cells that will be evaluated by the specified criteria. It is required.

criteria – here you specify your criteria. Also required.

sum_range - it's optional. These are the range of cells that you specify to sum up, in case you don't want those in the range argument. If you don't include the sum_range, then Excel adds the cells specified in the range argument.



In the following example, I want to calculate the total monthly salary of those with a Dr. title. So in cell B15 enter

=SUMIF(A2:A12;"Dr*";C2:C12)


instead of "Dr*" you could also enter "dr*" [=SUMIF(A2:A12,"dr*",C2:C12)] - it gives you the same reault: 12100 dollars. That means the SUMIF function is not case-sensitive.


Image


Here the function looks for the specified criteria that starts with "Dr*" within the range A2:A12 and sum up the corresponding values from the range C2:C12 and returns it: 12100 dollars.

Remember, you use an asterisk (*) to match any sequence of characters. Here "Dr*" asks to match all names that starts with Dr.




If you would use "D*" (or "d*") instead of "Dr*" (or "dr*"), it would then look for all names that starts with "D" and also incude Deborah Smith. The total would then change to 15700 dollars.


Image




Let's try something else:

In the following example, I will calculate the salary of CEO's only. So my criteria is "CEO".


Enter in cell B19

=SUMIF(B2:B12;"CEO";C2:C12)


Image


So the function looks for the specified criteria "CEO" within the range B2:B12 and sum up the corresponding values from the range C2:C12 and returns it: 7400 dollars.

Once again, instead of "CEO", you coud type "ceo". As mentioned, SUMIF function is not case-sensitive.



Also consult this page from Microsoft.



Have fun guys :-)
Post Reply