Here, we’ll walk you through some unique ways in which you can apply the function across a wide range of practical situations.
What Is the SUMIF Function?
SUMIF is a built-in function in Excel that you can use to sum up a range of values under certain conditions. It’s an incredibly useful function, and one that you should learn about if you’re going to be using Excel in any capacity.
The syntax for the SUMIF function can be expressed as follows:
You can perform advanced financial calculations in a variety of scenarios using the SUMIF function. Let’s see how this works.
For Freelancers
When you’re working as a freelancer, it can be tough to keep track of all the different projects and clients you have going on at any given time. The good news is that the SUMIF function can make this process much easier. Let’s take a look at a couple of common scenarios where this can help.
1. Calculate Client Hours Worked for a Specified Period
You can use the Excel SUMIF Function to calculate the monthly sum of hours worked for client projects. For instance, let’s say you want to calculate the individual monthly hours worked for all your clients.
Or, let’s say you want to count the number of hours you’ve worked for each client worked during January and then add this value to their account.
In this example, we have taken into consideration the total number of hours worked in January, and then extracted the number of hours worked for each client by specifying their name (for instance, “Client 4”, in our example).
2. View Invoices Due From Clients
As a freelancer, it’s important to manage your clients well. For instance, you might wish to know the invoice amounts due from your clients after a cut-off date.
In the example above, we’ve got an array of client names, contract dates, and invoice amounts outstanding. If you want to know how much money is owed by clients who have not paid their invoices after 01 January 2020, you will use a SUMIF function like the one below:
Using the formula above, we can sum up all outstanding amounts corresponding to the date values in Column I, which are greater than the cut-off date of 01/01/2020.
For Individual Use
SUMIF is a great function for any spreadsheet user. It’s easy to use and can help you get your work done faster.
Sometimes you may just want to keep track of your personal expenses—to calculate how much you owe on your credit card, how much you spend on groceries, or simply get up to speed on your favorite sports teams’ performance. Let’s take a closer look at how the SUMIF function can help you sort things out.
3. Track Your Favorite Sports Team’s Stats
SUMIF is a great way to keep track of your favorite sports team’s stats. It’s an easy way to see how many games they’ve won, their win/loss record, and their overall score.
For example, if you want to see how many goals your favorite team(s) or players have scored this season, you can use SUMIF to count them up for you. All you need to do is set up an equation that looks like this:
Using the function, you can check the total number of goals scored by each player or the number of times a team has won at home. Sounds fairly easy, doesn’t it?
4. Monitor Your Personal Expenses
The SUMIF function is a powerful tool to stay on track with your budget. If you want to keep track of your spending by category, you can use the SUMIF function to add up all the values in your spreadsheet that match a certain condition.
Let’s look at an example. Using the spreadsheet above, If you wanted to know how much money you spent on groceries this month, you could use the SUMIF function like this:
This would add up all the values in column E that matched the text criteria “Groceries”, which equals $140, in this case.
For Work or Business
The SUMIF function works well for many scenarios. It can provide solutions for your day-to-day tasks and help you manage your day better. Let’s explore some of the ways you can utilize this function for work or business.
5. Calculate Your Employees’ Bonuses
If you’re looking to calculate employee bonuses based on certain criteria (e.g., sales or revenue), the Excel SUMIF function is a great way to do it.
Say you have a spreadsheet containing employee data and the corresponding turnover or sales revenues generated for the months of July, August, and September, across different worksheets.
Further, you have a breakdown of the bonus amounts (Columns F14:G21) that are due once employees meet the eligibility criteria based on turnover. For instance, employees are eligible to receive a bonus of $600 if they have a sales turnover exceeding $5000 but less than $10,000.
Now, you want to calculate the bonus amounts due to Amy based on her turnover in the quarter ending September. We need to use more than one function here—SUMIF, SUMPRODUCT, and VLOOKUP, to arrive at the bonus amounts:
First, write down the worksheet names in the Summary sheet (I3:16). Taking into consideration the month of July only, note down the SUMIF function for Amy as shown below.
Now, you’ll need to introduce the SUMPRODUCT function to make sure everything gets added up accurately in the subsequent steps.
Next, you’ll need to sum up the corresponding values of sales turnover across all worksheets for Amy. For this, you’ll only need to consider the names of all worksheets in Column I. You’ll need to use the INDIRECT function to pull the sales data values across the three months. To do this, make the following adjustments to the SUMIF function:
For [July! B3:B8] - Replace with the worksheet range INDIRECT("’"&$I$4:$I$6&"’!"&“B3:B8”) For [July! D3:D8] - Replace with the worksheet range INDIRECT("’"&$I$4:$I6&"’!"&“D3:D8”)
The expression will look as follows:
This brings up the total sales for Amy for July, August, and September, which amounts to $6,892.
Now, to calculate the amount of bonus, we wrap the entire function inside that of yet another VLOOKUP function to look up the eligible bonus amounts in Columns F4 through G9. The final expression would look as follows:
Based on this formula, the bonus amount for Amy works out to be $600.
6. Track Google Page Views for Specific Items
The SUMIF function is a useful way to add up online page views for specified items in your online store. You can use it to count page views, visitors, social media engagement, or any other metrics that you want to track via Google Analytics or any other dashboard of your choice.
In our example, we’ll be taking a look at how we can sum up page views for website SKU items titled “Furniture” and “Decor items” for a bi-weekly period. We would simply have to sum up the individual page views using text criteria.
We can start by identifying the range of cells that contain our data, i.e., Columns C and D. Once we’ve selected our range, we’ll need to enter our criteria into a SUMIF function by typing in “Furniture”. We’ll need to repeat the process with another SUMIF function by typing in “Décor items” this time. To arrive at the sum of page views, we’ll simply need to wrap the two SUMIF functions in a SUM function.
This can be achieved by the following expression:
Once you know how to use SUMIF, you can easily sum up individual items (or rows) based on their criteria. This makes it super easy to evaluate your data, specifically the effectiveness of marketing campaigns. You can also use it for summing up social media engagement metrics, finding the best traffic sources, and examining overall engagement with your content.
SUMIF: A Powerful Tool for Data Analysis
We can’t stress enough how much the SUMIF function can help you work more efficiently. While it does have its limitations, you can still use it to create some pretty powerful formulae. Given its versatility, it can more than make up for its simplicity and save you a ton of effort. Start incorporating the SUMIF function into your spreadsheets today. It just takes a little practice.
