COUNTIF, SUMIF, AVERAGEIF,
COUNTIFS, SUMIFS, AVERAGEIFS,
MAXIFS, MINIFS
15 slides
Click the right arrow to view the next slide.
The Range-Based Conditional (RACON) functions are supercharged IF functions that perform aggregations.
The IF function tests the value of one cell and compares it to:
The logical test in an IF-function 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
your custom results
= value to be tested
= value the Test Value is compared to
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
(Click the up arrow to return or the right arrow for the next slide.)
Part 1
Part 2
Part 3
a single cell
1st argument
2nd argument
3rd argument
(1) a range and (2) the rest of the formula in a text string
Part 1
Part 2
Part 3
now a range of cells
this argument is a text string
1st argument
2nd argument
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 )
1st argument
2nd argument
You do this using some basic rules for forming literal text strings. Specifically:
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.
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.
Note:
If you leave out the comparison operator, Excel assumes it's equal to (=). So Part 2, the operator, can be omitted.
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" ).
UP NEXT: COUNTIF, SUMIF, & AVERAGEIF