Microsoft Office Tutorials and References

In Depth Information

Several of the examples in this section use the COUNTIFS function, which was intro-

duced in Excel 2007. I also present alternative versions of the formulas, which you

should use if you plan to share your workbook with others who use a version prior to

Excel 2007.

Using And criteria

An And criterion counts cells if all specified 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 greater than 100
and
less than or equal to 200. For this example, the COUNTIFS function will do the job:

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

The COUNTIFS function accepts any number of paired arguments. The first 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 preced-

ing 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”)

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

values that are greater than 200. The result is the number of cells that contain a value greater than 100 and less

than or equal to 200.

Creating this type of 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. An alternate technique is to use an array formula,

such as the one that follows. You may find creating this type of formula easier.

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

When you enter an array formula, remember to use Ctrl+Shift+Enter — and don't type

the curly brackets.

Sometimes, the counting criteria will be based on cells other than the cells being counted. You may, for ex-

ample, want to count the number of sales that meet the following criteria:

•
Month
is January,
and

• SalesRep is Brooks,
and