Karen has a Bachelors in Communications. She has 25 years of experience in Information Systems, Adult Learning and Virtual Training.
The COUNTIF Function
The COUNTIF function is another popular way to calculate data in an Excel worksheet. COUNTIF actually combines two functions, IF and COUNT. It allows you to count the number of cells that contain very specific data. The IF part is the condition, or criteria, that must be met in order for Excel to count. The second part, COUNT, totals up the number of cells that match your desired criteria.
In this lesson, you will learn about the COUNTIF function and learn how to use it in a formula.
So, the COUNTIF function totals up the number of cells in a range that meet a specified criteria or condition that you determine. When you write a formula using COUNTIF, think about it in two pieces: the criteria and the range where you want Excel to perform the counting. Or, in other words, what do you want Excel to find and where would you like Excel to look.
The syntax or structure of the formula would be =COUNTIF(range,criteria). Let's look at an example.
Where the Criteria Is Text
Imagine you have a home-based, cookie-making business called The Best Cookies on the Block. Each Monday, you receive orders from your regular customers, and you deliver the cookies to their door on Wednesday. You have a worksheet with the names of your 12 customers and what cookies they want for the week. Column A lists your customers and column B lists the cookie orders. You would like a formula that totals each type of cookie, so you know how much baking you will need to do. This also helps prepare for your trip to the store, so you can buy the right amount of ingredients.
You bake four different kinds of cookies: Snickerdoodles, Chocolate Chip, Oatmeal Raisin and Peanut Butter. In column A, below the list of orders, you have titled 4 rows with the name of each cookie. You would like to add formulas in column B, adjacent to the cookie titles, that total the number of orders for each cookie. In Cell B16, let's add our first formula that will count the orders for Peanut Butter cookies:
=COUNTIF(B3:B14, ''Peanut Butter'')
Notice that the name of the cookie or our criteria is in quotes. When asking Excel to look for specific text (as opposed to numbers, which do not need quotes), quotation marks on both sides of the text are an important part of the formula syntax or structure. But that's text. What if you are working with numbers?
Where the Criteria Is Numbers
Unless otherwise stated in the formula, the COUNTIF function assumes you want Excel to count cells that are equal to the criteria, such as our Peanut Butter cookies. Using COUNTIF to look for specific text is fairly straightforward. But the syntax is a bit different when using numbers or cell references in your formulas.
For instance, what if you want to find all the numbers that are greater than 10 or less than 5 or not equal to 7? Operators, such as greater than (>), less than (<) and not equal to (<>) can also be used in a formula using the COUNTIF function. Keep in mind, the formula would still have the two pieces, range and criteria. However, the way it is written (the syntax) is different. Let's look at an example.
In the case of our cookie business, the standard order is for 1 dozen cookies. In our worksheet, we added a column for how many dozen cookies the customer ordered. When the orders get to 3 dozen or more, you need to call in the troops to help with the baking. You would like a place on the worksheet that lets you know how many customers ordered more than 2 dozen. This also acts as a sort of warning that you will need to make a call for additional help.
The calculation we need Excel to perform is to count the number of cells in column C, where the number is greater than (>) 2. Here is the formula:
Notice the quotes on both sides of the operator. This is so Excel does not get the symbol confused with the actual criteria. Also, note the ampersand (&) symbol. This tells Excel you will be using a number (or it could be a cell reference such as B2), as the criteria - as opposed to text.
More Formula Examples
Here are more example formulas using the COUNTIF function that you may find helpful:
- =COUNTIF(C3:C14,''>='':&7) - Count the number of cells that have a number greater than or equal to 7
- =COUNTIF(C3:C4,''<>''&7) - Count the number of cells that are not equal to 7
- =COUNTIF(C3:C14,''<>''''Snickerdoodle'') - Count the number of cells that are not equal to Snickerdoodle
- =COUNTIF(C3:C14,''<''&B1) - Count the number of cells in a range that are less than (<) the contents of a particular cell (B1)
This lesson introduced you to the COUNTIF function in Excel. This function, when used in a formula, counts the number of cells where a specific criteria has been met. You learned that COUNTIF combines two functions: IF and COUNT.
You learned that there are two main parts to the syntax of the formula: the range and the criteria. The proper syntax is:
You also learned how to use COUNTIF in formulas, and we looked at different examples where text and numbers are used.
Next time you need to count the number of cells in a range where the value is very specific, try using the COUNTIF function.
Following this lesson, you'll have the ability to:
- Explain the usefulness of the COUNTIF function in Excel
- Identify the two functions that combine to produce the COUNTIF function and the two parts to the syntax of the formula
- Describe how to use the COUNTIF function in formulas
To unlock this lesson you must be a Study.com Member.
Create your account
Register to view this lesson
Unlock Your Education
See for yourself why 30 million people use Study.com
Become a Study.com member and start learning now.Become a Member
Already a member? Log InBack