Excel 4 Ur Business

Overview

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

• How many songs were sung by each singer?
• How many songs were not sung by Eminem?
• How many songs lasted at least 4 minutes?
• How many songs were longer than average?
• How many songs were sung by a singer whose last name begins with S?
• How many songs were sung by a singer whose last name contains six letters?
• How many songs were sung after June 15, 2005?
• How many songs were sung before the beginning of 2009?
• How many songs lasted exactly 4 minutes?

In a more general context, how do I perform operations such as

• 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 spreadsheet contains information about makeup sales, we might want to count how many sales transactions were made by the salesperson named Jennifer or how many sales transactions occurred after June 10. The COUNTIF function lets you count the number of cells in a range that meet criteria that are defined on the basis of a single row or column of the spreadsheet.

The syntax of the COUNTIF function is COUNTIF(range, criteria).

• Range is the range of cells in which you want to count cells meeting a given criterion.
• Criteria is a number, date, or expression that determines whether a given cell in the range is counted.

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.

To illustrate how to use these functions, consider a database that gives the following information for each song played on radio station WKRP:

• Singer
• The date the song was played
• The length of the song

The file Rock.xls, shown in the following figure, shows a subset of the data.

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 I choose Insert, Name, Create, and then choose 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 the following figure. 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 inthe following figure. I need to enclose <>Eminem in quotation marks because Excel treats the 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 & symbol to concatenate the reference to cell B5 and the <> operator.

How many songs lasted at least 4 minutes?

In cell C16, I’ve computed the number of songs played that lasted at least 4 minutes by using the formula COUNTIF(Minutes,”>=4″). You need to enclose >=4 in quotation marks because >=, like <>, is treated as text. We find that 477 songs lasted at least 4 minutes.

How many songs were longer than average?

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 compute 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 & sign. 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 a singer whose last name begins 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 thirtytwo songs were sung by singers with a last name that begins 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 a singer whose last name contains 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 with six characters, Britney Spears and Eminem, who together sang a total of 129 + 114 = 243 songs.)

How many songs were sung after June 15, 2005?

The criteria you use with COUNT 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 sung before the beginning of 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 lasted exactly 4 minutes?

In cell C22, I compute the number of songs lasting exactly 4 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 4 minutes. In a similar fashion, we found in cell C23 that 230 songs lasted exactly 5 minutes.

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 in the following figure.)

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.

Explore posts in the same categories: MS Excel Tune Up