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”