Microsoft Office Tutorials and References
In Depth Information
Advanced Counting Formulas
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 example above 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 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 brackets.
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:
h Month is January, and
h SalesRep is Brooks, and
h Amount is greater than 1,000
The following formula returns the number of items that meet all three criteria. Note that the
COUNTIFS function uses three sets of pairs of arguments.
=COUNTIFS(Month,”January”,SalesRep,”Brooks”,Amount,”>1000”)
An alternative formula, which works with versions prior to Excel 2007, uses the SUMPRODUCT
function. The following formula returns the same result as the previous formula:
=SUMPRODUCT((Month=”January”)*(SalesRep=”Brooks”)*(Amount>1000))
 
Search JabSto ::




Custom Search