Microsoft Office Tutorials and References
In Depth Information
Counting Cells That Meet Multiple Criteria
Counting Cells That Meet Multiple Criteria
Tip 116 presents examples of formulas that use the COUNTIF function. Those formulas are useful
for counting cells that match a single criterion. The formula examples in this tip are useful when
you need to count cells only if two or more criteria are met. These criteria can be based on the
cells that are being counted or based on a range of corresponding cells.
Some of these formulas use the COUNTIFS function, which was introduced in Excel
2007. For compatibility purposes, I also present an alternative formula that works
with previous versions of Excel.
Using “And” criteria
The 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 might want
to count cells that contain a value greater than 0 and less than or equal to 12. Any cell that has a
positive value less than or equal to 12 is included in the count.
The following formula, which works only in Excel 2007 and later, counts the cells in a range
named Data that fall between 0 and 12:
=COUNTIFS(Data,”>=0”,Data,”<=12”)
Arguments for the COUNTIFS function always come in pairs. This formula has two sets of paired
arguments: The first argument in each pair is the range of interest, and the second argument is
the criterion. Values are counted if they meet all criteria specified by each argument pair.
For compatibility with previous versions of Excel, use this formula:
=COUNTIF(Data,”>0”)-COUNTIF(Data,”>12”)
This formula counts the number of values that are greater than 0 and then subtracts the number
of values that are greater than 12. The result is the number of cells that contain a value greater
than 0 and less than or equal to 12.
Creating this type of formula can be confusing because the formula refers to the condition “>12”
even though the goal is to count values that are less than or equal to 12. An alternative technique is to
use an array formula, such as the following one (you might find creating this type of formula easier):
=SUM((Data>0)*(Data<=12))
When you enter an array formula, press Ctrl+Shift+Enter, not just Enter.

Search JabSto ::

Custom Search