Joined: 25 Sep 2007
|Posted: Sun Feb 19, 2017 1:08 pm Post subject: Excel: Some Examples with SUMIF Function
|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
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.
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.
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
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
Read wonderful articles (English & German) on Science & Tech, Environment, Health and many other topics only on BlogArena.
For comments of suggestions, please contact us: firstname.lastname@example.org