The SUMIFS function is pretty versatile, but it can be a little tricky if you’re new to it. If you’ve used Excel to manage your work and personal finances (and create some fun graphs and charts, of course), or are just curious about how this useful tool works, then you’ve come to the right place.
Here we’ll walk you through some unique ways you can use the feature in a variety of practical situations.
Table of Contents
What is the SUMIFS function?
SUMIF is a built-in function in Excel that you can use to sum a range of values under certain conditions. It’s an incredibly useful feature to learn about if you use Excel in any form.
The syntax for the SUMIFS function can be expressed as follows:
=SUMIF(range, criteria, [sum_range])With the SUMIF function, you can perform advanced financial calculations in a variety of scenarios. Let’s see how this works.
For freelancers
When you work as a freelancer, it can be difficult to keep track of all the different projects and clients you are working on at any given time. The good news is that the SUMIF function can make this process a lot easier. Let’s take a look at some common scenarios where this can be helpful.
1. Calculate customer hours worked for a given period
You can use Excel’s SUMIF function to calculate the monthly sum of hours worked on customer projects. Suppose you want to calculate the individual monthly hours worked for all your customers.
Or let’s say you want to count the number of hours you worked for each client who worked in January and then add that value to their account.
In this example, we took into account the total number of hours worked in January and then extracted the number of hours worked for each customer by providing their name (e.g. “Customer 4” in our example).
2. View customer invoices that are due
As a freelancer, it’s important to manage your clients well. For example, you might want to know what invoice amounts are due from your customers after a deadline.
In the example above, we have a series of customer names, contract dates and outstanding invoice amounts. If you want to know how much money is owed by customers who have not paid their bills after January 1, 2020, use a SUMIF function like this:
=SUMIF(I9:I14,">1/1/2020",J9:J14)
Using the above formula, we can sum up all outstanding amounts according to the dates in column I that are greater than the key date 01/01/2020.
For individual use
SUMIF is a great feature for any spreadsheet user. It is easy to use and can help you get your work done faster.
Sometimes you might 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 just to keep up with the performance of your favorite sports team. Let’s take a closer look at how the SUMIFS function can help you sort things.
3. Follow the stats of your favorite sports team
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 tally, and their total 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 for you. All you have to do is set up an equation that looks like this:
The feature allows you to check the total number of goals scored by each player or the number of times a team has won at home. Sounds pretty easy doesn’t it?
4. Monitor your personal expenses
The SUMIF function is a powerful tool for keeping track of your budget. If you want to track your expenses by category, you can use the SUMIF function to add up all the values in your spreadsheet that match a specific condition.
Let’s look at an example. If you want to know how much money you spent on groceries this month using the table above, you can use the SUMIFS function like this:
=SUMIF(D4:D18,"Groceries",E4:E18)This would add up all the values in column E that matched the text criterion “Food”, which in this case is $140.
For work or business
The SUMIFS function works well for many scenarios. It can provide solutions to your daily tasks and help you manage your day better. Let’s explore some of the ways you can use this feature for work or business.
5. Calculate your employees’ bonuses
If you want to calculate employee bonuses based on specific criteria (e.g. sales or revenue), the Excel SUMIF function is a great way to do it.
Suppose you have a table with employee data and the corresponding sales or sales revenue generated in separate worksheets for the months of July, August, and September.
You also have a breakdown of bonus amounts (columns F14:G21) that are due once employees meet eligibility criteria based on sales. For example, employees are eligible for a $600 bonus if their sales exceed $5,000 but less than $10,000.
Now you want to calculate the bonus amounts due amy based on their revenue for the quarter ended September. We need to use more than one function here – SUMIF, SUMPRODUCT and VLOOKUP to get the bonus amounts:
First, note the worksheet names on the summary sheet (I3:16). Consider only the month of July and record the SUMIF function for Amy as shown below.
Now you have to introduce them SUMPRODUCT Function to ensure everything is summed up accurately in the following steps.
Next, you need to sum up the corresponding sales values from all the worksheets for Amy. To do this, you only need to consider the names of all the worksheets in Column I You have to use those INDIRECT Function to pull the sales data values over the three months. To do this, make the following adjustments to the SUMIF function:
- To the [July! B3:B8] – Replace with the worksheet range INDIRECT(“‘”&$I$4:$I$6&”‘!”&”B3:B8”)
- To the [July! D3:D8] – Replace with the worksheet range INDIRECT(“‘”&$I$4:$I6&”‘!”&”D3:D8”)
The expression looks like this:
This brings Amy’s total sales for July, August, and September to $6,892.
Now, to calculate the bonus amount, we wrap the whole function in another VLOOKUP function to look up the allowable bonus amounts in columns F4 through G9. The final printout would look like this:
Based on this formula, Amy’s bonus amount is $600.
6. Track Google page views for specific articles
The SUMIF function is a useful way to add up online page views for specific items in your online store. You can use it to count page views, visitors, social media engagement or any other metric you wish to track via Google Analytics or any other dashboard of your choice.
In our example, let’s see how we can aggregate pageviews for website SKU items titled “Furniture” and “Decorative Items” over a two-week period. We would simply have to summarize the individual page views based on text criteria.
We can start by identifying the range of cells that will contain our data, i.e. columns C and D. Once we have selected our range, we need to enter our criteria into a SUMIF function by typing “furniture”. We need to repeat the process with another SUMIF function, this time typing “Decor Items”. To get the sum of page views, we simply need to wrap the two SUMIFS functions into one SUM function.
This can be achieved by the following expression:
=SUM(SUMIF(C3:C17,"Furniture",D3:D17)+(SUMIF(C3:C17,"Décor items",D3:D17)))Once you know how to use SUMIF, you can easily sum individual items (or rows) based on their criteria. This makes it super easy to evaluate your data, especially the effectiveness of marketing campaigns. You can also use it to summarize social media engagement metrics, find top traffic sources, and examine overall engagement with your content.
SUMIF: A powerful data analysis tool
We can’t stress enough how much the SUMIF function can help you work more efficiently. While it has its limitations, you can still use it to create some pretty powerful formulas. Given its versatility, it can more than make up for its simplicity and save you a ton of trouble. Start integrating the SUMIFS function into your spreadsheets today. It just takes a little practice.
This article was previously published on Source link
