RACON Functions:
COUNTIF, SUMIF, AVERAGEIF,
COUNTIFS, SUMIFS, AVERAGEIFS,
MAXIFS, MINIFS
15 slides
Click the right arrow to view the next slide.
RACON Functions:
The Logical Test
What's the deal with RACON Functions?
The RangeBased Conditional (RACON) functions are supercharged IF functions that perform aggregations.

The basic IF function takes the contents of a single cell and compares it to some value.
 RACONs compare the contents of an entire range of cells to some value.

The IF function returns your custom results in place of TRUE and FALSE.
 RACONs perform summary (aggregate) operations, like sum and average, on items that pass the test.
The IF function tests the value of one cell and compares it to:
 some particular value (number, text) you type in or
 some value from another cell (cell reference).
The logical test in an IFfunction statement is a logical formula and can be broken down into 3 parts:
=IF(A1>15, "Hooray!", "Boo!")
PART 1 PART 2 PART 3
Test Value Comparison Operator Comparison Value
The IF Function's Logical Test
your custom results
= value to be tested
= value the Test Value is compared to
The RACONs' Logical Test
RACON functions test multiple cells in a range. So, Part 1 of the logical test is no longer a reference to a single cell; it's a reference to a range of cells, like A1:A10.
Because of this, programmers found it more efficient to separate Part 1 into its own argument. Part 2 and Part 3 stayed together in a separate argument, but now have to be written as a text string.
(Click the down arrow to see a list of the comparison operators.)
These make up Part 2 of your logical test.
= equal to A1 = A2
> greater than A1 > A2
< less than A1 < A2
>= greater than or equal to A1 >= A2
<= less than or equal to A1 <= A2
<> not equal to A1 <> A2
FYI: Comparison Operators
(Click the up arrow to return or the right arrow for the next slide.)
The IF function's logical test
is one argument—a logical formula
Part 1
Part 2
Part 3
A1
>
15
=IF(
, TRUE, FALSE)
a single cell
1st argument
2nd argument
3rd argument
The RACON functions
split it into two arguments:
(1) a range and (2) the rest of the formula in a text string
Part 1
Part 2
Part 3
A1:A10, "
>
15")
now a range of cells
this argument is a text string
1st argument
2nd argument
=COUNTIF(
The RACONs' Logical Test
With RACON functions, logical tests are fed to the function in two arguments.
For example, the syntax for the simplest RACON, the COUNTIF function, is:
=COUNTIF( Part 1 , Part 2 & Part 3 together )
 Part 1 is always a reference to a range of cells.
 Part 2 and Part 3 are combined into a single string of text.
1st argument
2nd argument
Making Parts 2 & 3 a String of Text
You do this using some basic rules for forming literal text strings. Specifically:
 Part 2. Put comparison operators in quotes.

Part 3. Put numbers and text in quotes with the comparison operator.
 =COUNTIF(A1:D100, ">=15") Comparison value is a number
 =COUNTIF(A1:A10, "=Bob") Comparison value is text

Part 3. If the comparison value comes from another cell, you use a cell reference. That cell's contents are already considered a text string. So put the comparison operator in quotes, and then use the concatenate operator (&) to combine the two text strings into one.
 =COUNTIF(A1:A10, ">="&B1) Comparison value is in another cell
Making Parts 2 & 3 a String of Text
all in quotes
Part 3 not in quotes
Part 2 in quotes
=COUNTIF(A1:D100, ">=15") numbers
=COUNTIF(A1:A10, "=Bob") text
=COUNTIF(A1:A10, ">="&B1) cell references
all in quotes
In quotes or not, Part 2 and Part 3 make up just one text string, one argument.
Understanding the RACON Logical Test
Compared to logical formulas and the two basic logical functions IF and IFS, the syntax for the RACON logical test is weird and difficult for newcomers to understand.
 The first argument, Part 1, is easy. It's a range, like A1:A10.
 The second argument is easier to understand if you remember to make it a single string of text. Just combine Part 2 and Part 3 using "" and &.
Understanding the RACON Logical Test
Note:
 The first argument in the RACON logical test is called (predictably enough) range.
 The second argument is called criteria.*
 * Yes, I know it's grammatically incorrect, but that's what it is.
If you leave out the comparison operator, Excel assumes it's equal to (=). So Part 2, the operator, can be omitted.
 =COUNTIF(A1:A10, "15") is the same as =COUNTIF(A1:A10, "=15") numbers
 =COUNTIF(A1:A10, "Bob") is the same as =COUNTIF(A1:A10, "=Bob") text
 =COUNTIF(A1:A10, B1) is the same as =COUNTIF(A1:A10, "="&B1) cell reference
An Important Exception
If the comparison operator is equal to (=),
AND...
the comparison value is a number, you do not need to enclose that number in quotation marks.
=SUMIF( A1:A10, 5 )
gives the same result as
=SUMIF( A1:A10, "=5" ) and
=SUMIF( A1:A10, "5" ).
An Extension of that Exception
for Numbers
Fin
UP NEXT: COUNTIF, SUMIF, & AVERAGEIF
RACON Logical Test
By smilinjoe
RACON Logical Test
 785