RACON Functions:
COUNTIFS, SUMIFS, AVERAGEIFS,
MAXIFS, MINIFS
20 slides
Click the right arrow to view the next slide.
RACONs Ending in IF
COUNTIF takes two arguments:
- the range to be both tested and aggregated* and
- the criteria (operator and comparison value) for the test.
=COUNTIF(A2:A10, ">=15")
1st argument 2nd argument
* In the case of COUNTIF, the aggregation operation is counting.
Other aggregations are summing, averaging, and finding minimums and maximums.
RACONs Ending in IF
=SUMIF(A2:A10, "=United States", D2:D10)
1st argument 2nd argument 3rd argument
Because it's possible that the range to be tested is not the range to be aggregated, SUMIF and AVERAGEIF have an optional third argument:
- the range to be tested,
- the criteria for the test, and
- the sum_range or average_range.
RACONs Ending in IF
COUNTIF, SUMIF, and AVERAGEIF are RACONs because they can test an entire range of cells.
But they can only apply one test to those cells. Hence the singular IF at the end.
The IFS RACONs
The five IFS RACONS can apply multiple tests to multiple ranges (up to 127!) in one function statement.
- COUNTIFS
- SUMIFS
- AVERAGEIFS
- MINIFS
- MAXIFS
But, like the three IF RACONs, they will only perform one aggregation operation at the end.
IFS Argument Structure
Here is how the arguments are arranged in a RACON IFS function:
- The range to be aggregated is the first argument.*
- Then each of the multiple tests is presented as a pair of arguments:
- the range to be tested, and
- the criteria (comparison operator and comparison value).
(* Click the down arrow if you want to know why it's been moved from last to first.)
IFS Argument Structure
In SUMIF and AVERAGEIF, the argument for the range to be aggregated comes last, because it's optional.
But the range to be aggregated is NOT optional in the IFS RACONs. This is because, if you have multiple test ranges, Excel won't know which one you want aggregated. It may not be any of them.
Plus, from a programming standpoint, the range to be aggregated is the only range out of a possible 128 that's not followed by a logical test (criteria). So if you put it first and get it out of the way, the routine knows that everything after that is a criterion pair (a range and a test), resulting in more efficient processing.
(Click the up arrow to go back or the right arrow to go to the next slide.)
Example:
Average Cost of Bigville Oranges
Find the average Cost of Oranges in Bigville.
Example:
Average Cost of Bigville Oranges
First, determine the range to aggregate (average). It's the Cost column, D2:D10.
That's your first argument, average_range.
Example:
Average Cost of Bigville Oranges
Next, determine the range to test for the presence of the city Bigville. It's the City column, B2:B10.
That's your second argument, criteria_range1.
Example:
Average Cost of Bigville Oranges
Now add that test's criteria as a text string in quotes. That's "=Bigville".
That's your third argument, criteria1.
first test: a lone IF
(NOTE: If we stop here, with one test, it works like an AVERAGEIF statement.)
Example:
Average Cost of Bigville Oranges
Now add the next test: searching the Product column for Orange. The column is C2:C10 and the criteria is "=Orange".
They're the fourth and fifth arguments, criteria_range2 and criteria2.
first test
second test
IFS
Example:
Average Cost of Bigville Oranges
So now you've told Excel to average any value in D2:D10 that is in a row that has:
- the value Bigville in B2:B10, and
- the value Orange in C2:C10.
Let's apply these two tests as column filters to demonstrate how this works invisibly within the AVERAGEIFS function.
Example:
Average Cost of Bigville Oranges
Find the average Cost of Oranges in Bigville.
Example:
Average Cost of Bigville Oranges
The test B2:B10, "=Bigville" eliminates all the rows with Smallville in the City column, leaving only Bigville.
Example:
Average Cost of Bigville Oranges
The IFS function keeps moving through the tests from left to right until either (a) one of the tests fails to find anything that satisfies its criteria or (b) the function runs out of tests. In case of (b), it performs the aggregation.
Here, C2:C10,"=Orange" leaves us two rows to average.
Example:
Average Cost of Bigville Oranges
So, as you can see, each test in an IFS RACON works like a column filter in a table (or table-shaped range).
- The test B2:B10, "=Bigville" filters out all rows that don't have Bigville as their City.
- The test C2:C10, "=Orange" filters out all rows that don't have Orange as their Product.
The rows that are left get their Cost values (D2:D10) aggregated (into an average).
Ands and Ors
It's important to note that the IFS RACONs combine tests using AND. They don't work as OR combinations. For example:
- you can ask for City = Bigville AND Product = Orange,
- but you can't ask for City = Bigville OR Product = Orange.
There are ways to get OR results using wildcards, the addition operator, the OR function, and SUMPRODUCT function. See me for more on that, if you're interested.
Recap
- Use RACONs if you want to test entire ranges (usually columns) of cell values.
- There are 3 IF RACONs:
- COUNTIF, SUMIF, AVERAGEIF
- Use an IF RACON if you need only one test.
- There are 5 IFS RACONs:
- COUNTIFS, SUMIFS, AVERAGEIFS, MAXIFS, MINIFS
- Use an IFS RACON if you need multiple tests.
- In tables or table-shaped ranges, RACONs will perform their aggregate calculation on the rows that meet ALL the test criteria.
Why Bother With IF RACONs?
You probably noticed that anything you can do with an IF RACON function you can do with an IFS RACON function. In fact, you have to use IFS for doing maximums and minimums. So why not just do everything with IFS?
Because the certification test requires that you know the IF RACONs. When you get back to the real world, you can just do IFS.
Fin
RACON IFS Functions
By smilinjoe
