Microsoft Office Tutorials and References
In Depth Information
Using COUNT or COUNTA to Count Numbers or Nonblank Cells
Tip: Simulating LARGEIFS
LARGEIFS ,, PERCENTILEIFS
PERCENTILEIFS , and
, and QUARTILEIFS
The function arguments of 13 through 19 allow the array to be calcu-
lated on the fly. The formula in B20 of Figure 11.4 is a wild, over-the-
top formula that seems like it would have come from the ExcelGurus
Gone Wildbook I compiled in 2008.
The goal is to find records that match multiple criteria and then to
apply the LARGE function to those matching records. The array ar-
gument starts out with the sales amounts in D9:D16. But then the sales
amounts are divided by a Boolean expression.
(B9:B16="East") checks to see whether the record is in the East re-
gion. (C9:C16) checks to see whether the rep is R1. When you multiply
these two conditions together, you get an array of ones and zeros. The
1 indicates both conditions are TRUE. In the figure, the result would be
When the formula evaluates the sales amounts divided by the array
of ones and zeros, you either end up with the sales amount or a
division-by-zero error. In the figure, the result of 16000 divided by 0
is #DIV/0!. The result of 1000 divided by 1 is 1000, and so on. The array
contains mostly #DIV/0! errors and a few actual numbers. Because the
AGGREGATE function has an option to ignore error values, the result
is that the function simulates doing LARGE with multiple conditions.
to Count Numbers or Nonblank Cells
A number of functions process nonblank cells. =COUNT counts all the nu-
meric or date cells in a range. =COUNTA counts all the nonblank cells in a
COUNT oor COUNTA
COUNTA to Count Numbers or Nonblank Cells
You can find COUNT and COUNTA in the Statistical drop-down under
the More Functions icon of the Formulas tab.