Syntax of COUNTIF Formula
Example of COUNTIF Formula
Possible Errors returned by the COUNTIF Formula
COUNTIF formula in Excel counts the numbers of cells in a range that meet the given condition.
COUNTIF Formula Syntax
COUNTIF Formula has two parts:
COUNTIF (range, criteria)
range
range is the range in which the value that you are trying to find out is located.
criteria
criteria is the value whose occurence in the above range we would like to count. The criteria can be a string (Ex “Bill”), a number (Ex. 15) or a combined expression (“>=”&15).
Example of a COUNTIF Formula
Using COUNTIF to find the number of times a string appears in a range
Let’s look at an example of the COUNTIF formula. Suppose we wanted to find out the number of time a particular value (“Bill Wanton”) appears in the range as shown in the example above. We could simply write =COUNTIF(A2:A10,F2). The result in this case will be 4 as shown in the example above.
Using COUNTIF to find the number of times a string appears in a range using the wildcard character
Now if we wanted to find the number of times a name starting with “T” appears in the range. The formula =COUNTIF(A2:A10,”T*”) would have done the trick here.
Using COUNTIF as an Array Formula
Now suppose we wanted to know the number of times each name appears in the range. The simple way to do is to simply write =COUNTIF($A$2:$A$10,A2) in an adjacent column. However if we wanted to, we would have used the array formula and entered it as =COUNTIF(A2:A10,A2:A10) with a CTRL+SHIFT+ENTER. (See Array Formulas for greater details)
Using COUNTIF to find the number of times a numeric value appears in a range
We have seen the example of the above where we try to find out the occurrence of a single string. The formula pretty much stays the same if you want to count the number of times a numeric value appears in the range. Say you wanted to find the number of times sales have been less than 100 units. You could find that out using =COUNTIF(D2:D10,”< =100″).
Using COUNTIF with a combined expression
Using COUNTIF to find the number of times a numeric value appears using a combined expression – is where COUNTIF gets interesting. Suppose you wanted to find the number of times a manger does not appear in the list. You could write =COUNTIF(A2:A10,”<>”&F2) where the cell F2 contains the name of the manager. This way, whenever the value of cell F2 changes, the formula returns the new value.
How to enter the COUNTIF formula in an Excel Sheet
1. Select the cell in which you want to place the formula
2. Type the formula as =COUNTIF(
3. Then using the keyboard up-down and left-right arrow key, move the cursor to the first (top-left) cell of the range in which we would like to count the occurrence of a string.
4. Keeping the SHIFT button pressed, move the cursor to the last cell (bottom-right) of the range.
4. Press the comma key (,)
5. Then either enter the value you would like to know the frequency of appearance of – if its a string enclose in quotes (Ex. “Bill’), if its a number enter as such, if its an expression enter it within quotes (Ex. “< =100”) along with the “&” operator (Ex “<=”&F2) (F2 is the cell which contains the value and we are trying to find out all occurrences which are less than this value)
6. Close the formula bracket by typing in the closing bracket ).
Check out the clip above for knowing if the values you’ve entered are in the same order. In the end your formula should look something like this =COUNTIF(D2:D10,”< =100″)
Possible Errors with the COUNTIF Formula
The COUNTIF formula can result in the following error values:
COUNTIF #NAME? Error
The #NAME? Error value in COUNTIF formula can occur when you incorrectly specify the range in first parameter. Suppose you mistyped A2 as AAA2 in the formula (Ex. =COUNTIF(AAA2:A10,F2) ) you will get a #NAME? error.
You can download an example of COUNTIF formula here or click on the button below:
Hi there,
I was just wndering if you could help me determine a formula. I’ve been tryign to keep track of things and who I’ve done them with, and I want to see the number of times each person appears. So in my C column I have names listed. Such as “Jen, Darcy, Gary” in C1, and just “Darcy” in C2. And I was wondering if there was a formula I could use to find how many times each name appeared?
Thanks!
September 19th, 2009 at 6:19 pmBob,
I can’t think of a single formula that can do this without being overtly complicated. If you haven’t already tried, you can use the FIND formula to find the occurrence of a substring within a larger string and then use the COUNT formula (or COUNTIF =1) to get the frequency of occurrence. The first part would be:
It’s a single formula that can be copied across cells and then if you put on a filter, that could help.
September 19th, 2009 at 11:43 pmRegards,
Hi,
Could anybody help me on this:
I’m trying to use this formula to count two parameters. Example:
How many “M” are there in the letterRange “B”.
Range(“D7”).Formula = “=COUNTIF(LetterRange,””b””)+ Countif(GenderRange,””M””)”
I keep getting back the wrong numbers.
Thanks
July 8th, 2010 at 10:02 pmmany thanks
November 14th, 2016 at 7:22 am