Use the COUNTIFS function in Excel 2007 to analyze data
Microsoft Office Excel 2007: Data Analysis and Business Modeling
By Wayne L. Winston
Wayne L. Winston is a professor of Decision Sciences at Indiana University's Kelley School of Business and has earned numerous MBA teaching awards. For 20+ years, he has taught clients at Fortune 500 companies how to use Excel to make smarter business decisions. Wayne and his business partner, Jeff Sagarin, developed the player-statistics tracking and rating system used by the Dallas Mavericks professional basketball team. He is also a two time Jeopardy! champion.
To learn more about other books on the 2007 Microsoft Office system, visit Microsoft Press.
In this article
Suppose I have a list of songs that are played on the radio. For each song, I know the singer, the date the song was played, and the length of the song. How can I answer questions such as those listed below about the songs in the list?
-
How many were sung by each singer?
-
How many were longer than the average length of all songs on the list?
-
How many were sung by singers whose last names begin with S?
-
How many were played after June 15, 2005?
-
How many were played before 2009?
-
How many were exactly four minutes long?
-
How many were sung by my favorite singer and were exactly four minutes long?
In a more general context, how do I perform operations such as the following?
-
Count the number of cells in a range containing numbers.
-
Count the number of blank cells in a range.
-
Count the number of nonblank cells in a range.
We often want to count the number of cells in a range that meet a given criterion. For example, if a worksheet contains information about makeup sales, we might want to count the number of sales transactions made by the salesperson named Jennifer, or the number of sales transactions that occurred after June 10. This article shows you how to use the COUNTIF function to count the number of cells in a range that meet criteria that are defined on the basis of a one row or column of the worksheet.
COUNTIF function syntax
The syntax of the COUNTIF function is COUNTIF(range,criterion).
-
Range is the range of cells in which you want to count cells meeting a given criterion.
-
Criterion is a number, date, or expression that determines whether to count a given cell in the range.
The syntax of COUNTIFS (new in Microsoft Office Excel 2007) is COUNTIFS(range1,criterion1,range2,criterion2,…,range_n,criterion_n).
COUNTIFS will count the number of rows for which the range1 entry meets criterion1, the range2 entry meets criterion2, the range_n entry meets criterion_n, and so on. Thus, COUNTIFS allows the criteria to involve more than one column or multiple conditions in one column.
Examples of criteria Excel accepts for COUNTIF
The key to using the COUNTIF function (and other similar functions) successfully is understanding the wide variety of criteria that Excel will accept. The types of criteria you can use are best explained through the use of examples. In addition to examples of the COUNTIF function, I'll provide examples of the COUNT, COUNTA, and COUNTBLANK functions:
-
The COUNT function counts the number of cells in a range containing numbers.
-
The COUNTA function counts the number of nonblank cells in a range.
-
The COUNTBLANK function counts the number of blank cells in a range.
As an illustration of how to use these functions, consider a database that gives the following information for each song played on radio station WKRP:
-
The singer
-
The date the song was played
-
The length of the song
Figure 1 shows a subset of the data used in the following examples.
How many songs were sung by each singer?
To begin, I select the first row of the database, the range D6:G6. Then I select the whole database by pressing CTRL+SHIFT+DOWN ARROW. Next, in the Defined Names group on the Formulas tab, I clicked Create From Selection, and then chose Top Row. We have now named the range D7:D957 Song Numb, the range E7:E957 Singer, the range F7:F957 Date, and the range G7:G957 Minutes. To determine how many songs were sung by each singer, we copy from C5 to C6:C12 the formula COUNTIF(Singer,B5). In cell C5, this formula now displays the number of cells in the range Singer that match the value in B5 (Eminem). The database contains 114 songs sung by Eminem. Similarly, Cher sang 112 songs, and so on, as you can see in Figure 2. I could have also found the number of songs sung by Eminem with the formula COUNTIF(Singer,"Eminem"). Note that you must enclose text such as Eminem in quotation marks (" ") and that criteria are not case sensitive.
How many songs were not sung by Eminem?
To solve this problem, you need to know that Excel interprets the character combination <> as "not equal to." The formula COUNTIF(Singer,"<>Eminem"), entered in cell C15, tells us that 837 songs in the database were not sung by Eminem, as you can see in Figure 3. I need to enclose <>Eminem in quotation marks because Excel treats the not equal to (<>) character combination as text and Eminem is, of course, text. You could obtain the same result by using the formula COUNTIF(Singer,"<>"&B5), which uses the ampersand (&) symbol to concatenate the reference to cell B5 and the <> operator.
How many songs were at least four minutes long?
In cell C16, I've computed the number of songs played that lasted at least four minutes by using the formula COUNTIF(Minutes,">=4"). You need to enclose >=4 in quotation marks because the greater than or equal to (>=) character combination, like <>, is treated as text. We find that 477 songs lasted at least four minutes.
How many songs were longer than the average length of all songs on the list?
To answer this question, I first computed in cell G5 the average length of a song with the formula AVERAGE(Minutes). Then, in cell C17, I computed the number of songs that last longer than the average with the formula COUNTIF(Minutes,">"&G5). I can refer to another cell (in this case G5) in the criteria by using the & character. You can see that 477 songs lasted longer than average, which matches the number of songs lasting at least 4 minutes. The reason these numbers match is that I assumed the length of each song was an integer. For a song to last at least 3.48 minutes, it has to last at least 4 minutes.
How many songs were sung by singers whose last names begin with S?
To answer this question, I use a wildcard character, the asterisk (*), in the criteria. An asterisk represents any sequence of characters. Thus the formula COUNTIF(Singer,"S*") in cell C18 picks up any song sung by a singer whose last name begins with S. (The criteria are not case sensitive.) Two hundred thirty-two songs were sung by singers with last names that begin with S. This number is simply the total of the songs sung by either Bruce Springsteen or Britney Spears (103+129=232).
How many songs were sung by singers whose last names contain exactly six letters?
In this example, I used the question mark (?) wildcard character. The question mark matches any character. Therefore, entering the formula COUNTIF(Singer,"??????") in cell C19 counts the number of songs sung by singers having six letters in their last name. The result is 243. (Two singers have last names of six characters, Britney Spears and Eminem, who together sang a total of 243 songs —129+114=243.)
How many songs were played after June 15, 2005?
The criteria you use with COUNTIF functions handle dates on the basis of a date's serial number. (A later date is considered larger than an earlier date.) The formula COUNTIF(Date,">6/15/2005") in cell C20 tells us that 98 songs were sung after June 15, 2005.
How many songs were played before 2009?
We want our criteria to pick up all dates on or before December 31, 2008. I've entered in cell C21 the formula COUNTIF(Date,"<=12/31/2008"). We find that 951 songs (which turns out to be all the songs) were sung before the start of 2009.
How many songs were exactly four minutes long?
In cell C22, I compute the number of songs lasting exactly four minutes with the formula COUNTIF(Minutes,4). This formula counts the number of cells in the range G7:G957 containing a 4. We find that 247 songs lasted exactly four minutes. In a similar fashion, we found in cell C23 that 230 songs lasted exactly five minutes.
How many songs were sung by Bruce Springsteen and were exactly four minutes long?
We want to count each row where an entry in the Singer column is Springsteen and an entry in the Minutes column is 4. This is a job for the wonderful new COUNTIFS function. Simply enter in cell C24 the formula =COUNTIFS(Singer,"Springsteen",Minutes,4).
This formula counts any row in which Singer is Springsteen and Minutes equals 4. We find that Bruce Springsteen sang 24 songs that were exactly four minutes long. My favorite Springsteen song is "Thunder Road," but that song is more than four minutes long.
How many songs were sung by Madonna and were three to four minutes long?
Because we are dealing with multiple criteria, this is again a job for COUNTIFS. Entering in cell C25 the formula =COUNTIFS(Singer,"Madonna",Minutes,"<=4",Minutes,">=3") counts all rows in which Madonna sang a song that was from three to four minutes long. These are exactly the rows we wish to count. We find that Madonna sang 70 songs that were from three to four minutes long (my favorite one is "Crazy for You!").
How do I count the number of cells in a range containing numbers?
The COUNT function counts the number of cells in a range containing a numeric value. For example, the formula COUNT(B5:C14) in cell C2 displays 9 because nine cells (the cells in C5:C13) in the range B5:C14 contain numbers. (See Figure 2.)
How do I count the number of blank cells in a range?
The COUNTBLANK function counts the number of blank cells in a range. For example, the formula COUNTBLANK(B5:C14) entered in cell C4 returns a value of 2 because two cells (B14 and C14) in the range B5:C14 contain blanks.
How do I count the number of nonblank cells in a range?
The COUNTA function returns the number of nonblank cells in a range. For example, the formula COUNTA(B5:C14) in cell C3 returns 18 because 18 cells in the range B5:C14 are not blank.
create a formula that will always yields number of songs played today for these question
ReplyDelete