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:
Month: A2:A10
Region: B2:B10
Sales: C2:C10
CD-ROM | This workbook (including the formula examples) is available on the companion CD-ROM. The file is named counting and summing examples.xlsx . |
Table 3-3 contains formulas that demonstrate a variety of counting techniques.
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 |
Table 3-4 shows a number of formula examples that demonstrate a variety of summing techniques.
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 ways to count or sum cells that meet certain criteria are:
Filtering (using a table)
Advanced filtering
The DCOUNT and DSUM functions
Pivot tables
For more information, consult the Help system.