# Count values that meet a condition with the COUNTIF function

(This post was first published in March 2011. Since lots of you want to learn more about the COUNT function, we're republishing it again. Let us know in the Comments section what other things you want to know about COUNT. )

[NOTE: Near the bottom of this post, I've listed some updated how-tos on embedding Excel and PowerPoint files on web pages.]

You probably know how to use the COUNT function to count cells that contain a value. If you need a refresher on counting, see COUNT function, Ways to count values in a worksheet, and Video: Count cells in Excel.

But what if you want to count only the cells that meet a condition, such as being greater than or equal to a number or date you specify, or that matches text? That's where the COUNTIF function comes in really handy.

To use COUNTIF, you first specify the range that contains the values you want to count. Then you enter a criterion (condition) that's used as a test. Here's COUNTIF with both the range (B2:B5) and the criterion (">55"). The function inspects the range B2:B5, applies the condition "greater than 55," and then returns the number of values that meet the condition and displays that number in the worksheet. Pretty easy, plenty powerful.

Below is a live worksheet, thanks to the Excel Web App embedding feature of Live.com. Take a look at the formulas, the description, the result, and especially the "How it works" information. And because it's a live worksheet, you can practice right here by entering formulas of your own.

You can download this workbook by clicking the View full-size workbook button in the lower-right corner of the embedded workbook (at the right end of the black bar, above). Clicking the button loads the workbook in a new browser window (or tab),where you'll see a Download button. Note that you can't type in the worksheet cells in the full size view.

For information about advanced settings for embedding a workbook, see Customize how your Excel workbook is embedded.

To learn more about COUNTIF, see COUNTIF function. To learn about an even more powerful function introduced in Excel 2007 that lets you use multiple criteria and ranges, see COUNTIFS function.

And finally, if you already know about the COUNTIF function and use it on a regular basis, do you have any tips to share? We'd love to hear more about how people are using this function.

New!

-- Gary Willoughby

• Good afternoon Señor Gary... I was wondering if you could help me with this question

In Excel 2007, how do I go about counting how many cells in a row are yellow shaded????

Wish you well, Henry

• Hi, Henry -- there's no worksheet function you can use to do this, but you can use a user-defined VBA function. Excel MVP Allen Wyatt provides the code and explains how it works here:

excel.tips.net/T001978_Counting_Colors_of_Cells.html

Or, if your cells are in the same column, you can apply a filter based on color. Then, select the cells, and on the Excel status bar, you can see the Count of selected cells.

I'm sure you'll find plenty more methods by just searching the web on "count cells in excel based on color."

• I want a formula that says if a cell contains a word with "POOL" in it then it will count the particular cell.  Example, if the cell = "Pool Cafe" then I want it to recognize the cell.  If the Cell says "Terrace Pool" I want it to recognize the cell.  The key driver being POOL.  Any suggestions?