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.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
