Working with IF Function Variations

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.