Microsoft Office Tutorials and References

In Depth Information

**Basic Counting Formulas**

Creating a pivot table is a quick way to get a count or sum of items without using formulas. Like

the database function, using a pivot table is appropriate when your data is in the form of a

worksheet database or table.

Refer to Chapter 18 for information about pivot tables.

Basic Counting Formulas

The basic counting formulas presented here are all straightforward and relatively simple. They

demonstrate how to count the number of cells in a range that meet specific criteria. Figure 7-1

shows a worksheet that uses formulas (in column E) to summarize the contents of range A1:B10 —

a 20-cell range named
Data.

Figure 7-1:
Formulas provide various counts of the data in A1:B10.

About this chapter’s examples

Most of the examples in this chapter use named ranges for function arguments. When you adapt

these formulas for your own use, you’ll need to substitute either the actual range address or a

range name defined in your workbook.

Also, some examples are array formulas. An
array formula,
as explained in Chapter 14, is a special

type of formula. You can spot an array formula because it is enclosed in brackets when it is

displayed in the Formula bar. For example

{=Data*2}

When you enter an array formula, press Ctrl+Shift+Enter (not just Enter). And don’t type the

brackets — Excel inserts the brackets for you. If you need to edit an array formula, don’t forget

to press Ctrl+Shift+Enter when you’ve finished editing. Otherwise, the array formula will revert

to a normal formula, and it will return an incorrect result.