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
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:
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:
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.
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