Microsoft Office Tutorials and References

In Depth Information

**Counting cells based on multiple criteria**

Several of the examples in this section use the
COUNTIFS
function, which was introduced in Excel 2007. The text

also presents alternative versions of the formulas, which you should use if you plan to share your workbook with

others who use an earlier version of Excel.

Using And criteria

An And criterion counts cells if all speciﬁ ed conditions are met. A common example is a

formula that counts the number of values that fall within a numerical range. For example,

you may want to count cells that contain a value that’s greater than 100
and
less than or

equal to 200. For this example, the
COUNTIFS
function will do the job:

17

=COUNTIFS(Amount,">100",Amount,"<=200")

If the data is contained in a table, you can use table referencing in your formulas. For example, if the table is named

Table1, you can rewrite the preceding formula as:

=COUNTIFS(Table1[Amount],">100",Table1[Amount],"<=200")

This method of writing formulas does not require named ranges. Excel automatically creates names for the table and

each column in the table.

The
COUNTIFS
function accepts any number of paired arguments. The ﬁ rst member of the

pair is the range to be counted (in this case, the range named Amount); the second member

of the pair is the criterion. The preceding example contains two sets of paired arguments

and returns the number of cells in which Amount is greater than 100 and less than or equal

to 200.

Prior to Excel 2007, you would need to use a formula like this:

=COUNTIF(Amount,">100")-COUNTIF(Amount,">200")

This formula counts the number of values that are greater than 100 and then subtracts the

number of values that are greater than or equal to 200. The result is the number of cells

that contain a value greater than 100 and less than or equal to 200. This formula can be

confusing because the formula refers to a condition
">200"
even though the goal is to

count values that are less than or equal to 200.

Yet another alternate technique is to use an array formula, like the one that follows. You

may ﬁ nd it easier to create this type of formula:

{=SUM((Amount>100)*(Amount<=200))}