Microsoft Office Tutorials and References
In Depth Information
Counting and Summing Techniques
Counting formula examples
Table 3-3 contains formulas that demonstrate a variety of counting techniques.
Table 3-3: Counting Formula Examples
Formula
Description
=COUNTIF(Region,”North”)
Counts the number of rows in which Region = “North”
=COUNTIF(Sales,300)
Counts the number of rows in which Sales = 300
=COUNTIF(Sales,”>300”)
Counts the number of rows in which Sales > 300
=COUNTIF(Sales,”<>100”)
Counts the number of rows in which Sales <> 100
=COUNTIF(Region,”?????”)
Counts the number of rows in which Region contains
five letters
=COUNTIF(Region,”*h*”)
Counts the number of rows in which Region contains the
letter H (not case-sensitive)
=COUNTIFS(Month,”Jan”,Sales,”>200”)
Counts the number of rows in which Month = “Jan” and
Sales > 200 (Excel 2007 and later)
{=SUM((Month=”Jan”)*(Sales>200))}
An array formula that counts the number of rows in
which Month = “Jan” and Sales > 200
=COUNTIFS(Month,”Jan”,Region,”North”)
Counts the number of rows in which Month = “Jan” and
Region = “North” (Excel 2007 and later)
{=SUM((Month=”Jan”)*(Region=”North”))}
An array formula that counts the number of rows in
which Month = “Jan” and Region = “North”
=COUNTIFS(Month,”Jan”,Region,”North”)+
COUNTIFS(Month,”Jan”,Region,”South”)
Counts the number of rows in which Month = “Jan” and
Region = “North” or “South” (Excel 2007 and later)
{=SUM((Month=”Jan”)*((Region=”North”)+
(Region=”South”)))}
An array formula that counts the number of rows in
which Month = “Jan” and Region = “North” or “South”
=COUNTIFS(Sales,”>=300”,Sales,”<=400”)
Counts the number of rows in which Sales is between
300 and 400 (Excel 2007 and later)
{=SUM((Sales>=300)*(Sales<=400))}
An array formula that counts the number of rows in
which Sales is between 300 and 400
Summing formula examples
Table 3-4 shows a number of formula examples that demonstrate a variety of summing
techniques.
Table 3-4: Summing Formula Examples
Formula
Description
=SUMIF(Sales,”>200”)
Sum of all Sales over 200
=SUMIF(Month,”Jan”,Sales)
Sum of Sales in which Month = “Jan”
=SUMIF(Month,”Jan”,Sales)+SUMIF(Month,”Feb”,Sales)
Sum of Sales in which Month =”Jan” or “Feb”                                                                                                 Search JabSto ::

Custom Search