- How to Count N...
- How to Count Ce...
- How to Join Cel...
- How to calculat...
- How to Add Numb...
- How to Subtract...
- How to Divide ...
- How to Multipl...
- How to Calcula...
- How to Hide Ro...
- How to Insert ...
- How to Edit Co...
- How to Delete C...
- How to Draw Bo...
- How to Add Numb...
- How to Wrap Te...
- How to Merge C...
- How to use Tran...
- How to Highligh...
- How to use Cond...
- How to Make Col...
- How to Calculat...
- How To Use Numb...
- How To Use IF F...
- How To Use Coun...
- How to Use Date...
- How To Use Past...
- How To Use Hour...
- How To Sort Da...
- How To Use Dat...
- How To Use Mont...
- How To Use Dict...
- How To Use Hype...
- How To Do Trans...
- How To Covert N...
- How To Use Full...
- 7 Ways to Use V...
- How to Insert C...
- 7 Ways to Use V...
- Excel Shortcuts...
- Learn Excel Onl...
- Use Checkbox in...
- Use Formula in ...
- How to Use Form...
- Find Duplicate ...
- Use Vlookup wit...
- How To Make Lis...
- How to make Spa...
- How to make Com...
- 4 Types of Prog...
- How to Make Pro...
- How to make Sal...
- How To Use Text...
- How to do Data...
- How to Make Pay...
- How to Make Quo...
- How to Make Pri...
- Find Duplicate ...
- How To Do Data...
- Use Countif Fun...
- Vlookup with If...
- How To Make Lis...
- How to calculat...
- Convert Row to ...
- 10 Ways to Use ...
- How to Use Aver...
- How to Use Filt...
- Create Invoice ...
- Compare 2 Lists...
- How to Sort Dat...
- 12 Number Forma...
- Text Formatting...
- 5 Ways To Use A...
- 3 Ways to Use H...
- Vlookup for Beg...
- 8 Ways to Use C...
- Nested IF Formu...
- Show Formulas i...
- How to Use Macr...
- Sales Report So...
- If Formula in E...
- 4 Advanced Pivo...
- How To Stop Dup...
- Make Pivot Tabl...
- How To Remove d...
- Change Number t...
- Hlookup in Exce...
- MIS Report in E...

- How to Count Numbers in ...
- How to Count Cells Contai...
- How to Join Cells in Exce...
- How to calculate running ...
- How to Add Numbers in Ex...
- How to Subtract Numbers i...
- How to Divide Numbers in...
- How to Multiply Numbers ...
- How to Calculate Percent...
- How to Hide Rows in Exce...
- How to Insert Comments I...
- How to Edit Comment In E...
- How to Delete Comments In...
- How to Draw Borders In E...
- How to Add Numbers in Exc...
- How to Wrap Text in exce...
- How to Merge Cells in Ex...
- How to use Transpose Form...
- How to Highlight Cell in ...
- How to use Conditional Fo...
- How to Make Column Chart ...
- How to Calculate LCM In E...
- How To Use Number Formatt...
- How To Use IF Formula In ...
- How To Use CountBlank For...
- How to Use Date Formula ...
- How To Use Paste Special ...
- How To Use Hours, Minutes...
- How To Sort Data In Exce...
- How To Use Date Formula ...
- How To Use Month Formula ...
- How To Use Dictionary in ...
- How To Use Hyperlink in E...
- How To Do Translation in ...
- How To Covert Number to C...
- How To Use Full Screen V...
- 7 Ways to Use Vlookup in ...
- How to Insert Checkbox in...
- 7 Ways to Use Vlookup in ...
- Excel Shortcuts...
- Learn Excel Online...
- Use Checkbox in Excel to ...
- Use Formula in Pivot Tabl...
- How to Use Formula in Piv...
- Find Duplicate Value in E...
- Use Vlookup with If Formu...
- How To Make List in Excel...
- How to make Sparkline Cha...
- How to make Comparative M...
- 4 Types of Progress Chart...
- How to Make Progress Char...
- How to make Sales Dashboa...
- How To Use Text to Column...
- How to do Data Entry Wor...
- How to Make Payment Due R...
- How to Make Quotation in ...
- How to Make Price List in...
- Find Duplicate Remove Dup...
- How To Do Data Entry Wor...
- Use Countif Function in E...
- Vlookup with If Formula i...
- How To Make List in Excel...
- How to calculate Rank in ...
- Convert Row to Column in ...
- 10 Ways to Use Paste Spec...
- How to Use Averageifs For...
- How to Use Filter In Exce...
- Create Invoice in Excel...
- Compare 2 Lists in Excel...
- How to Sort Data in Excel...
- 12 Number Formatting Opti...
- Text Formatting with Wrap...
- 5 Ways To Use Averageif F...
- 3 Ways to Use Hlookup in ...
- Vlookup for Beginners in ...
- 8 Ways to Use Countif For...
- Nested IF Formula In Exce...
- Show Formulas in Dropdown...
- How to Use Macro in Excel...
- Sales Report Software in ...
- If Formula in Excel...
- 4 Advanced Pivot Table Op...
- How To Stop Duplicate Val...
- Make Pivot Table From Mul...
- How To Remove duplicate i...
- Change Number to Text in ...
- Hlookup in Excel...
- MIS Report in Excel...

Use COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list.

In its simplest form, COUNTIF says:

=COUNTIF(Where do you want to look?, What do you want to look for?)

For example:

=COUNTIF(A2:A5,"London")

=COUNTIF(A2:A5,A4)

Syntax

Examples

To use these examples in Excel, copy the data in the table below, and paste it in cell A1 of a new worksheet.

Data

Data

apples

32

oranges

54

peaches

75

apples

86

Formula

Description

=COUNTIF(A2:A5,"apples")

Counts the number of cells with apples in cells A2 through A5. The result is 2.

=COUNTIF(A2:A5,A4)

Counts the number of cells with peaches (the value in A4) in cells A2 through A5. The result is 1.

=COUNTIF(A2:A5,A2)+COUNTIF(A2:A5,A3)

Counts the number of apples (the value in A2), and oranges (the value in A3) in cells A2 through A5. The result is 3. This formula uses COUNTIF twice to specify multiple criteria, one criteria per expression. You could also use the COUNTIFS function.

=COUNTIF(B2:B5,">55")

Counts the number of cells with a value greater than 55 in cells B2 through B5. The result is 2.

=COUNTIF(B2:B5,"<>"&B4)

Counts the number of cells with a value not equal to 75 in cells B2 through B5. The ampersand (&) merges the comparison operator for not equal to (<>) and the value in B4 to read =COUNTIF(B2:B5,"<>75"). The result is 3.

=COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,">85")

Counts the number of cells with a value greater than (>) or equal to (=) 32 and less than (<) or equal to (=) 85 in cells B2 through B5. The result is 3.

=COUNTIF(A2:A5,"*")

Counts the number of cells containing any text in cells A2 through A5. The asterisk (*) is used as the wildcard character to match any character. The result is 4.

=COUNTIF(A2:A5,"?????es")

Counts the number of cells that have exactly 7 characters, and end with the letters "es" in cells A2 through A5. The question mark (?) is used as the wildcard character to match individual characters. The result is 2.

Common Problems

Problem

What went wrong

Wrong value returned for long strings.

The COUNTIF function returns incorrect results when you use it to match strings longer than 255 characters.

To match strings longer than 255 characters, use the CONCATENATE function or the concatenate operator &. For example, =COUNTIF(A2:A5,"long string"&"another long string").

No value returned when you expect a value.

Be sure to enclose the criteria argument in quotes.

A COUNTIF formula receives a #VALUE! error when referring to another worksheet.

This error occurs when the formula that contains the function refers to cells or a range in a closed workbook and the cells are calculated. For this feature to work, the other workbook must be open.

Download Practice Files from

http://www.myelesson.org/downloads

* Useful Excel formulas and Functions **

10 Most Used Formulas MS Excel-https://youtu.be/KyMj8HEBNAk

Learn Basic Excel Skills For Beginners- https://youtu.be/3kNEv3s8TuA

Learn the Basic of Excel for Beginners -https://youtu.be/qeMSV9T1PoI

10 Most Used Excel Formula-https://youtu.be/2t3FDi98GBk

Learn Vlookup Formula For Beginners in Excel-https://youtu.be/vomClevScJQ

5 Excel Questions Asked in Job Interviews-https://youtu.be/7Iwx4AMdij8

Create Speedometer Chart In Excel-https://youtu.be/f6c93-fQlCs

Create Pareto Chart In Excel-https://youtu.be/2UdajrDMjRE

How to Create Dashboard in Excel-https://youtu.be/RM8T1eYBjQY

Excel Interview Questions & Answers-https://youtu.be/Zjv1If63nGU