COUNTIF Formula Excel – How to use Excel COUNTIF Function




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)

countif-formula

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

countif-formula-example

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:

download example of COUNTIF function


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. Bob Parsons wrote:

    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!

  2. Ajay wrote:

    Bob,

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

  3. Dik Bahlz wrote:

    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

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel