COUNTIFS, SUMIFS, AVERAGEIFS,

MAXIFS, MINIFS

20 slides

Click the right arrow to view the next slide.

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*.

=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*.

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 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.

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.)

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.)

Find the average Cost of Oranges in Bigville.

First, determine the range to aggregate (average). It's the **Cost** column, D2:D10.

That's your first argument, *average_range*.

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*.

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.)

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

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.

Find the average Cost of Oranges in Bigville.

The test B2:B10, "=Bigville" eliminates all the rows with *Smallville* in the **City** column, leaving only *Bigville*.

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.

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).

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.

- 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.

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*.