Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

I spend quite a bit of time reading the Excel newsgroups on the Internet, and it seems that many of the questions deal with conditional counting or summing. In an attempt to answer most of these questions, I present a number of formula examples that deal with counting various things on a worksheet, based on single or multiple criteria. You can adapt these formulas to your own needs.

NEW  

Excel 2007 includes two new counting and summing functions that aren't available in previous versions (COUNTIFS and SUMIFS). Therefore, I present two versions of some formulas: an Excel 2007 “only version and an array formula that works with all recent versions of Excel.

Figure 3-8 shows a simple worksheet to demonstrate the formulas that follow. The following range names are defined:

Figure 3-8: This simple worksheet demonstrates some useful formulas for counting and summing.

CD-ROM  

This workbook (including the formula examples) is available on the companion CD-ROM. The file is named  counting and summing examples.xlsx .

Counting formula examples

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

=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

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

Open table as spreadsheet

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"

=SUMIFS(Sales,Month,"Jan",Region,"North")

Sum of Sales in which Month="Jan" and Region="North"

=SUMIFS(Sales,Month,"Jan",Region,"North")

Sum of Sales in which Month="Jan" and Region="North" (Excel 2007 only)

{=SUM((Month="Jan")*(Region="North")*Sales)}

An array formula that returns the sum of Sales in which Month="Jan" and Region="North"

=SUMIFS(Sales,Month,"Jan",Region,"<>North")

Sum of Sales in which Month="Jan" and Region <> "North" (Excel 2007 only)

{=SUM((Month="Jan")*(Region<>"North")*Sales)}

An array formula that returns the sum of Sales in which Month="Jan" and Region <> "North"

=SUMIFS(Sales,Month,"Jan",Sales,">=200")

Sum of Sales in which Month="Jan" and Sales>=200 (Excel 2007 only)

{=SUM((Month="Jan")*(Sales>=200)*(Sales))}

An array formula that returns the sum of Sales in which Month="Jan" and Sales>=200

=SUMIFS(Sales,Sales,">=300",Sales,"<=400")

Sum of Sales between 300 and 400 (Excel 2007 only)

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

An array formula that returns the sum of Sales between 300 and 400

Other counting tools

Other ways to count or sum cells that meet certain criteria are:

For more information, consult the Help system.

Related

Категории