RACON Functions:

SUMIF, COUNTIF, AVERAGEIF

11 slides

Click the right arrow to view the next slide.

The RACON Functions

RACON functions compare values in a range of cells to some value of your choice. The cells in that range that return a TRUE result get aggregated into a count, a sum, an average, a minimum, or a maximum.

There are eight RACON functions. Three end in IF, and five end in IFS. We'll start with the ones that end in IF.

What Type of Range?

Technically, the RACONs don't really care, but the RACON functions are most effective when your ranges are columns, and those columns stacked together in tabular form, the perfect format.

So from here on out, my range examples will be columns.

COUNTIF

The simplest RACON, COUNTIF, takes two arguments.

Range. The first argument is the range to be tested (compared).
Criteria. The second argument is a text string which includes (a) the comparison operator and (b) the value the range's cell values will be compared to.

=COUNTIF(A1:A10, ">15")

This example checks all the values in cells A1:A10 and counts those that are greater than (>) 15.

These test values...

are compared to this comparison value.

SUMIF and AVERAGEIF

SUMIF and AVERAGEIF take the same two arguments as COUNTIF and add an optional third argument.

This third argument is a range, like the first argument. It is for situations where the range to be tested isn't the range you want to aggregate.

=SUMIF(A1:A10, "=John Smith", B1:B10)

=SUMIF(B1:B10, ">0")

These are the data you want to sum.

These are the data you want to test.

Sometimes it works out that the data you want to test is also the data you want to add up (sum).

Woot! No need for that optional third argument.

That Third Argument

COUNTIF doesn't need that third argument, because data you can test can always be counted. Counting works on either text or numbers. Or dates (which are specially formatted numbers.)

So no need for a separate third argument.

That Third Argument

With SUMIF and AVERAGEIF, though, we may want to test a column of text, but we can't perform summing or averaging on that text. We can only count it.
Or you may want to test a column of numbers but aggregate a different column of numbers.
So in those cases, SUMIF and AVERAGEIF need a separate third argument to tell Excel which column to sum or average when it's not also the column to be tested.

That Third Argument

This third argument is called, respectively,

sum_range and
average_range.

We can use our logical test to effectively eliminate all rows except those on which we wish to perform the aggregation.

Now, when we arrange data in tabular form

Testing for C2:C10 "=FL" eliminates all rows but those whose State is FL. Now when we declare D2:D10 as the range to, say, sum, it will only add up D cells from rows with FL as the State.

=SUMIF(C2:C10, "=FL", D2:D10)

= D2 + D9 + D10

= 466 + 402 + 188

If any cell in Column C equals FL

add its counterpart in Column D to the sum

When we arrange data in tabular form

Fin

UP NEXT: COUNTIFS, SUMIFS, AVERAGEIFS, MINIFS, MAXIFS