Excel 2010 Bible

Chapter 13: Creating Formulas That Count and Sum

289

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

Note
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. n

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

The 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
     find it easier to create this type of formula:

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

Note
When you enter an array formula, remember to use Ctrl+Shift+Enter but don’t type the brackets. Excel
includes the brackets for you. n

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

may, for example, want to count the number of sales that meet the following criteria:

l Month is January, and

l (^) SalesRep is Brooks, and
l Amount is greater than 1000