Microsoft Office Tutorials and References

In Depth Information

**Using COUNT or COUNTA to Count Numbers or Nonblank Cells**

Tip: Simulating

Tip: Simulating
LARGEIFS

LARGEIFS
,,
PERCENTILEIFS

PERCENTILEIFS
, and

, and
QUARTILEIFS

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

{0;1;0;0;0;0;1;0}.

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.

Using

Using
COUNT

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

range.

COUNT
oor
COUNTA

COUNTA
to Count Numbers or Nonblank Cells

Caution

You can find COUNT and COUNTA in the Statistical drop-down under

the More Functions icon of the Formulas tab.

Syntax

=COUNT(value1,value2,...)