SUMIF Function; Ten Different Ways to Use It

SUMIF Excel is a function that effectively add all numbers that are given to cells' range, using the criteria it was given. When desiring to apply different criteria, then SUMIF function is an effective method for performing that task.

SUMIF Arguments

SUMIF is a function that has three arguments: range, criteria and sum_range.

Range: This is the range of cells, where the user desire to apply a criteria against.

Criteria: This is used for determining the cell(s) to add.

Sum_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.

Example 1: 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.


Find Revenue Sales for a Specific Year

Example 2: 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.


Knowing Expenses of a Specific Year

Example 3: 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.


Sum of Outsourced Expenses

Example 4: 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.


Assessing New Deals

Example 5: 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.


Employees Performance

Example 6: 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.


Provisions for Sales over a Specific Amount

Example 7: 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.


Combining SUMIF with VLOOKUP

Example 8: 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.


Using SUMIF with Table

Example 9: 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.


Using SUMIF to Find a Specific Number in Excel with Table

Example 10: 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.


Using SUMIF with a Specific Date

These are the most common sumif business cases in Excel.