How To Use SUMIF Function In Excel
The syntax of the function:
=SUMIF(range,criteria,[sum_range])
Mandatory argument range is the range of cells you want to estimate the function. This is the range of cells, where the user desire to apply a criteria against.
The criteria argument is used to determine the criteria against which the function will be summarized cells. It is possible to use the criterion in terms of numbers, Boolean expression, or text.
Sum_range use the optional argument, if we want to identify the specific cells that will be summed, where the corresponding cell in the field meet the criteria. If omitted, the function suma_range will add up all cells in the range. This is an optional argument, because it is the cells that you’d might like to sum. However, if this part of argument is omitted, then the function would make use of range as its sum_range.
Usage of SUMIF explains the following example.
To calculate the sum of people employed in Arizona, the cell type in the formula:
=SUMIF(A2:A11,”Arizona”,C2:C11)
As you can see, in this case the range of cells, where we check the criterion (A2: A11) is different from that which we value sum (C2: C11). Please also note that the criterion for entering text, enclose it in quotation marks. In the case of the criterion being the number of quotes can be omitted.
To calculate the total income in units employing more than 450 people, we use the formula:
=SUMIF(C2:C11,”>450″,D2:D11)
Like the search text, logical expressions apprehend in quotation marks.
Find Revenue (Sales) for a Specific Year
In this scenario, we have long experience and variety in our earnings, and we have a situation where the tax office, or any other governmental institution or other people whom needs to know what the company has made in 2006. At top side of the photo, we have written that we are looking for the information that tells how much the company made in 2006. There are multiple reasons you’d want know your revenue for 2006, and regardless the reason, this SUMIF function in the Microsoft Excel would easily have that knowledge at your disposal within a few seconds.
=SUMIF(A4:A17,2006,B4:B17)
Knowing Expenses of a Specific Year
The SUMIF function is perfect for this kind of situation, where the knowledge we strives after is within a specific area. We want to know how much we have paid for a specific period of time. As the case is in this scenario, we finds that expenses for 2005.
=SUMIF(A6:A19,”=”&B1:B1,C6:C19)
Sum of Outsourced Expenses
In this example, it was necessary to find out how much the company has spent on outsourcing for a project. But we want to find out how frequently the company has spent more than 10 000 dollars. The reason varies, but in this circumstances, it was understood as problematic, and we’d know that within a month the company has spent over hundred thousands of dollars outsourcing. This can be useful for assessing and finding out how the company can decrease its outsourcing expenses.
=SUMIF(A5:A13,”>10000″,C5:C13)
Assessing New Deals
In this example, a company wants to assess if it is best to make a new deal with the post office or choose any of the express posting companies to make it more convenient for its customers. However, the company policies determine that the company can only make such a deal when the company is shipping to the state at least 2000 times in a month.
=SUMIF(B4:B18,”Florida”,C4:C18)
Employees Performance
The SUMIF function is being used in this section when the company has set a specific goal that each employee should meet on a weekly basis, and a general weekly goal for the company. This function can be used on every end of the week (Friday or Saturday) to calculate the total of the ones whom did not meet the criteria. The minimum goal is 40 per week for each employee. The productiveness of this function requires knowing the overall goals, which in this case is 280. We are using the SUMIF function to find out the answers.
=SUMIF(G4:G10,”>40″)
Provisions for Sales over a Specific Amount
We have made a lot of sales. But not all of them are equally profitable for the company. We have decided that we would like to know which provisions made us over a specific amount of money. In this example, the percentage of provision we’d get is 35% for every sales, and we want to know which sales have given the company over 70000 US dollars. Further, we would also like to exclude the tax from the provision, so we could find the net provision.
=SUMIF(C4:C6,”>70000″&0.35)
Combining SUMIF with VLOOKUP
We have an issue with our data, because we do not know how to find a specific number. The situation is that we have been in company for almost a year, and we do like to look for a specific year, but at the same time we want to use the SUMIF function. We decided to use the function in combination with VLOOKUP.
=SUMIF(C4:C13,VLOOKUP(C4,C4:C13,1,FALSE))
Using SUMIF with Table?
In this example, we do have knowledge about the sales performance of a company. But, we would like to know the performance of an individual (Bianca). There are other functions that are useful in doing this, but it does not exclude the SUMIF function. We use SUMIF function to know Bianca’s performance.
=SUMIF(Names,”Bianca”,Earnings)
Using SUMIF to Find a Specific Number in Excel with Table
In this example, we want to know if the company has ever made a specific amount of money. This can be for example, we have said that within the upcoming six months, we need to have made a certain amount of money. We have the whole income in an Excel document, and we want to know if we have actually made a specific amount of money. We have set it at as a table, and now using SUMIF, with regards to the table.
=SUMIF(Table3[[#All],[Income]],”250000″,Table3[[#All],[Income]])
Using SUMIF with a Specific Date
We have different dates, and we would like to know how the expenses of before the date that have been set in the SUMIF function. In this example, we are going to use the SUMIF function to find the amount for different expenses before the date we have set, and it would be beneficial to the information that we are trying to get.
=SUMIF(A2:A12,”<2014-10-11″,C2:C12)
These are the most common SUMIF business cases in Excel.
Template
Further reading: Basic concepts Getting started with Excel Cell References
