Discover the power of IF Function Variations as you explore the versatile SUMIF and AVERAGEIF functions in Excel. Learn how these conditional functions allow you to sum or average a series of cells based on specific criteria, providing more targeted insights from your data.
Key Insights
- The IF versions of functions, such as SUMIF and AVERAGEIF, are an alternative version of standard functions that allow for conditional operations.
- SUMIF and AVERAGEIF functions perform summing or averaging of a series of cells or ranges based on specified criteria.
- These functions contain three arguments: a range, criteria to look for in that range, and then the range to either SUM or AVERAGE.
- The criteria argument can be either numeric or text. Numeric criteria do not require quotation marks, but text criteria do.
- Using these functions, you can perform operations like totaling the number of items in stock from different vendors or averaging their costs and prices.
- The IF variations of functions provide a useful way to filter data before applying mathematical operations, allowing for more specific and insightful data analysis.
IF Function Family
-
IF
Basic: IF(condition, true_value, false_value).
-
IFS
Multi-condition cleaner than nested IFs: IFS(c1, v1, c2, v2, ...).
-
SUMIFS / COUNTIFS
Aggregate based on multiple conditions in one formula.
-
IFERROR
Catch errors gracefully: IFERROR(formula, fallback_value).
Working with IF Function Variations
The IF versions of functions are an alternative version of – functions you probably already know – and the two we’re going to look at here are SUMIF and AVERAGEIF. And their names tell you which functions they’re very similar to.
The way they work is that instead of just summing or averaging a series of cells or ranges, they allow you to say “Sum these cells IF they meet a particular criteria” and, of course, the same goes for averaging. The function says, “Average these cells IF they meet criteria I establish in the function.”
Both functions contain three arguments – a range, criteria to look for in that range, and then the range to either SUM or AVERAGE. If you’ve watched the COUNT functions video, you’ve had a sneak peek, via the COUNTIF function that works the same way, except it only has 2 arguments, range and criteria, because it’s only counting how many times the criteria value is found in the same range that contains the criteria itself.
To clarify and demonstrate this, here in my Inventory worksheet, we’re going to total the number of items in stock from 3 different vendors and then average their costs and prices.
The first range we’ll use will be the items in inventory – in the Quantity on Hand column – and the criteria will be found in the Vendor column, where we’ll ask Excel to look for each vendor, one at a time, and apply the SUM and AVERAGE functions accordingly.
Let’s do this first for ABC Widgets.
To SUM the inventory on hand for stock that came from ABC Widgets, I click in cell E35, and type:
=SUMIF
And then press TAB to insert the opening parentheses. Note that the argument it asks for first is Range, which is the range that contains the Vendor names.
I drag through cells C5 through C29, and then type a comma, to move to the next argument.
Now Excel wants the criteria, which is ABC Widgets, in quotation marks.
Note that you only need the quotes if you’re searching for text. If you’re searching a numeric value, you can just type the number to look for – though using quotes in either case, which prevents forgetting them for text, won’t hurt the number search.
Next, I type the second comma, which activates the request for the third argument, which is the SUM range, or the range containing the numbers to sum if the criteria is found. This will be cells F5 through F29.
When I close the parentheses and press ENTER, I get the total items in stock that we got from ABC Widgets.
Watch as I repeat that, step by step, for Widget World and Widgets R Us.
Now, let’s AVERAGE the cost of widgets from each of those vendors. It’s the same set of arguments, but we’re averaging the Vendor Cost and Unit Price columns.
Note that instead of typing the function again from scratch, I can copy it from the first cell to the remaining 2 – and just adjust the cell addresses and criteria.
SO – the IF variations are a handy twist on functions we use frequently – allowing us to filter the numbers before they’re SUM’ed or AVERAGE’d.
