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
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)
=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
RACON IF Functions
By smilinjoe
