When and How to Use the SUMIF Function in Excel
The SUMIF function in Microsoft Excel is an incredibly useful and time-saving tool available to all users. Best of all, it’s easy to use and solves a wide range of problems for Sales, Marketing, and Financial reports or imports.
SUMIF(range, criteria, [sumrange])
The SUMIF function serves multiple purposes, including – allowing you to add up the sum of cells that meet specific criteria or matching specific criteria and placing a value in a cell. To show this, we’re going to use a very basic example.
You manage a sales team of five people. Each person’s individual goal is to sell $65,000 in balls, giving you a total goal of $333,000. You can use the SUMIF function to see if your team reached the goal. In this example, you can see that between your five sales team members, the total ball sales over $65,000 were $210,000, which means some members met the goal while others did not.
Range: This is required. This is the range of cells that you want to be evaluated by criteria.
Criteria: This is also required. It is the criteria in the form of a number, expression, cell reference, text or a function that defines which cells will be added. For example, this can be 50, “<35”, C7, “balls” or TODAY().
Sum Range: While this isn’t required it does provide a handy use in itself. If you are updating a price list from a vendor or needing to match up multiple sets of data with the same items the SUMIF formula is a perfect solution.
In the case below we are using the SUMIF formula to match the value of 12 (SUMRANGE) to the Range in Column A, which matches our CRITERIA in Column E. Once I have this formula set I can drag or copy down to the rest of my values in Column E to provide these corresponding values.
While this formula will save you a nice bit of time, imagine if you had an entire report that would simply refresh with up to date numbers. BizInsight and BizBroadcast will transform the way you build and distribute reports. When you combine these Excel tips with our powerful software, baked into Excel, you’ll start to understand what we mean when we say BizNet Software Makes Excel Reporting Fun.