Back
Excel

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.

 

The COUNTIF function in the formula bar

 

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