Counting formula examples

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

Table 3-3 contains formulas that demonstrate a variety of counting techniques. Table 3-3: COUNTING FORMULA EXAMPLES

Open table as spreadsheet

Table 3-3 contains formulas that demonstrate a variety of counting techniques. Table 3-3: COUNTING FORMULA EXAMPLES

Open table as spreadsheet

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

Formula

Description

=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 only)

{=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 only)

{=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 only)

{=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 only)

{=SUM((Sales>=300)*(Sales<=400))}

An array formula that counts the number of rows in which Sales is between 300 and 400

Was this article helpful?

0 0

Post a comment