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))}
Search JabSto ::

Custom Search