COUNTIF in Excel - count if not blank, greater than, duplicate or unique

Microsoft Excel provides several functions purposed for counting different kinds of cells, such as blanks or non-blanks, with number, date or text values, containing specific words or character, etc.

In this article, we will focus on the Excel COUNTIF function that is purposed for counting cells with the condition you specify. First, we will briefly cover the syntax and general usage, and then I provide a number of examples and warn about possible quirks when using this function with multiple criteria and specific types of cells.

In essence, COUNTIF formulas are identical in all Excel versions, so you can use the examples from this tutorial in Excel 2016, 2013, 2010 and 2007.

COUNTIF function in Excel - syntax and usage

Excel COUNTIF function is used for counting cells within a specified range that meet a certain criterion, or condition.

For example, you can write a COUNTIF formula to find out how many cells in your worksheet contain a number greater than or less than the number you specify. Another typical use of COUNTIF in Excel is for counting cells with a specific word or starting with a particular letter(s).

The syntax of the COUNTIF function is very simple:
COUNTIF(range, criteria)

As you see, there are only 2 arguments, both of which are required:

  • range - defines one or several cells to count. You put the range in a formula like you usually do in Excel, e.g. A1:A20.
  • criteria - defines the condition that tells the function which cells to count. It can be a number, text string, cell reference or expression. For instance, you can use the criteria like these: "10", A2, ">=10", "some text".

And here is the simplest example of Excel COUNTIF function. What you see in the image below is the list of the best tennis players for the last 14 years. The formula =COUNTIF(C2:C15,"Roger Federer") counts how many times Roger Federer's name is on the list:
An example of Excel COUNTIF function

Note. A criterion is case insensitive, meaning that if you type "roger federer" as the criteria in the above formula, this will produce the same result.

Excel COUNTIF function examples

As you have just seen, the syntax of the COUNTIF function is very simple. However, it allows for many possible variations of the criteria, including wildcard characters, the values of other cells, and even other Excel functions. This diversity makes the COUNTIF function really powerful and fit for many tasks, as you will see in the examples that follow.

COUNTIF formula for text and numbers (exact match)

In fact, we discussed the COUNTIF function that counts text values matching a specified criterion exactly a moment ago. Let me remind you that formula for cells containing an exact string of text: =COUNTIF(C2:C15,"Roger Federer"). So, you enter:

  • A range as the first parameter;
  • A comma as the delimiter;
  • A word or several words enclosed in quotes as the criteria.

Instead of typing text, you can use a reference to any cell containing that word or words and get absolutely the same results, e.g. =COUNTIF(C1:C9,C7).

Similar COUNTIF formulas work for numbers as well as for text values. As you can see in the screenshot below, the formula =COUNTIF(D2:D9,5) perfectly counts cells with quantity 5 in Column D.
Excel COUNTIF formula for numbers

COUNTIF formulas with wildcard characters (partial match)

In case your Excel data include several variations of the keyword(s) you want to count, then you can use a wildcard character to count all the cells containing a certain word, phrase or letters as part of the cell's contents.

Suppose, you have a list of tasks assigned to different persons, and you want to know the number of tasks assigned to Danny Brown. Because Danny's name is written in several different ways, we enter "*Brown*" as the search criteria =COUNTIF(D2:D10, "*Brown*").
A COUNTIF formula with wildcard characters for partial match

An asterisk (*) is used to find cells with any sequence of leading and trailing characters, as illustrated in the above example. If you need to match any single character, enter a question mark (?) instead, as demonstrated below.

Tip. It is also possible to use wildcards with cell references with the help of the concatenation operator (&). For example, instead of supplying "*Brown*" directly in the formula, you can type it in some cell, say F1, and use the following formula to count cells containing "Brown": =COUNTIF(D2:D10, "*"&F1&"*")

Count cells beginning or ending with certain characters

You can use either wildcard character, asterisk (*) or question mark (?), with the criterion depending on which exactly result you want to achieve.

If you want to know the number of cells that start or end with certain text no matter how many other characters a cell contains, use these formulas:

=COUNTIF(C2:C10,"Mr*") - count cells that begin with "Mr".

=COUNTIF(C2:C10,"*ed") - count cells that end with the letters "ed".

The image below demonstrates the second formula in action:
Excel COUNTIF formula to count cells that end with certain text

If you are looking for a count of cells that start or end with certain letters and contain the exact number of characters, you use the Excel COUNTIF function with the question mark character (?) in the criteria:

=COUNTIF(D2:D9,"??own") - counts the number of cells ending with the letters "own" and having exactly 5 characters in cells D2 through D9, including spaces.

=COUNTIF(D2:D9,"Mr??????") - counts the number of cells starting with the letters "Mr" and having exactly 8 characters in cells D2 through D9, including spaces.

Tip. To find the number of cells containing an actual question mark or asterisk, type a tilde (~) before the ? or * character in the formula. For example, =COUNTIF(D2:D9,"*~?*") will count all cells containing the question mark in the range D2:D9.

Excel COUNTIF for blank and non-blank cells

These formula examples demonstrate how you can use the COUNTIF function in Excel to count the number of empty or non-empty cells in a specified range.

COUNTIF not blank

In some of other Excel COUNTIF tutorials, you may come across formulas for counting non-blank cells in Excel similar to this one:


But the fact is, the above formula counts only cells containing any text values, meaning that cells with dates and numbers will be treated as blank cells and not included in the count!

If you need a universal COUNTIF formula for counting all non-blank cells in a specified range, here you go:


This formula works correctly with all value types - text, dates and numbers - as you can see in the screenshot below.
Excel COUNTIF formula to count non-blank cells


If you want the opposite, i.e. count blank cells in a certain range, you should adhere to the same approach - use a formula with a wildcard character for text values or another one (with the "" criteria) to count all empty cells.

Formula to count cells not containing any text=COUNTIF(range,"<>"&"*")

Translated into plain English, the criteria used in the above formula ("<>"&"*") means to find cells not equal to *, i.e. not containing any text in the specified range.

Universal COUNTIF formula for blanks (all value types)=COUNTIF(range,"")

The above formula correctly handles numbers, dates and text values. For example, the formula =COUNTIF(C2:C11,"") returns the number of all empty cells in the range C2:C11.

Note. Please be aware that Microsoft Excel provides another function for counting blank cells  =COUNTBLANK(range). For instance, the below formulas will produce exactly the same results as the COUNTIF formulas you see in the screenshot above:

Count blanks: =COUNTBLANK(C2:C11)

Count non-blanks: =ROWS(C2:C11)*COLUMNS(C2:C11)-COUNTBLANK(C2:C11)

Also, please keep in mind that both =COUNTIF(range,"") and =COUNTBLANK(range) count cells with formulas that only look empty. If you do not want to treat such cells as blanks, use this formula instead: =ROWS(C2:С11)*COLUMNS(C2:С11)-COUNTIF(C2:С11,"<>"&"").

For more information about counting blanks and not blanks in Excel, see the following tutorials: 3 ways to count empty cells in Excel and How to count non-empty cells in Excel.

COUNTIF greater than, less than or equal to

To count cells with values greater than, less than or equal to the number you specify, you simply add a corresponding operator to the criteria, as shown in the table below.

Please pay attention that in COUNTIF formulas, an operator with a number are always enclosed in quotes.

Criteria Formula Example Description
Count if greater than =COUNTIF(A2:A10,">5") Count cells where value is greater than 5.
Count if less than =COUNTIF(A2:A10,"<5") Count cells with values less than 5.
Count if equal to =COUNTIF(A2:A10,"=5") Count cells where value is equal to 5.
Count if not equal to =COUNTIF(A2:A10,"<>5") Count cells where value is not equal to 5.
Count if greater than or equal to =COUNTIF(C2:C8,">=5") Count cells where value is greater than or equal to 5.
Count if less than or equal to =COUNTIF(C2:C8,"<=5") Count cells where value is less than or equal to 5.

You can also use all of the above formulas to count cells based on another cell value, you will just need to replace the number in the criteria with a cell reference.

Note. In case of a cell reference, you have to enclose the operator in quotes and add an ampersand (&) before the cell reference. For example, to count cells in the range D2:D9 with values greater than a value in cell D3, you use this formula =COUNTIF(D2:D9,">"&D3):
Excel COUNTIF formula to count cells greater than another cell's value.

If you want to count cells that contain an actual operator as part of the cell's contents, i.e. the characters ">", "<" or "=", then use a wildcard character with the operator in the criteria. Such criteria will be treated as a text string rather than a numeric expression. For example, the formula =COUNTIF(D2:D9,"*>5*") will count all cells in the range D2:D9 with contents like this "Delivery >5 days" or ">5 available".

Using Excel COUNTIF function with dates

If you want to count cells with dates that are greater than, less than or equal to the date you specify or date in another cell, you proceed in the already familiar way using formulas similar to the ones we discussed a moment ago. All of the above formulas work for dates as well as for numbers. Let me give you just a few examples:

Criteria Formula Example Description
Count dates equal to the specified date. =COUNTIF(B2:B10,"6/1/2014") Counts the number of cells in the range B2:B10 with the date 1-Jun-2014.
Count dates greater than or equal to another date. =COUNTIF(B2:B10,">=6/1/2014") Count the number of cells in the range B2:B10 with a date greater than or equal to 6/1/2014.
Count dates greater than or equal to a date in another cell, minus x days. =COUNTIF(B2:B10,">="&B2-"7") Count the number of cells in the range B2:B10 with a date greater than or equal to the date in B2 minus 7 days.

Apart from these common usages, you can utilize the COUNTIF function in conjunction with specific Excel Date and Time functions such as TODAY() to count cells based on the current date.

Criteria Formula Example
Count dates equal to the current date. =COUNTIF(A2:A10,TODAY())
Count dates prior to the current date, i.e. less than today. =COUNTIF(A2:A10,"<"&TODAY())
Count dates after the current date, i.e. greater than today. =COUNTIF(A2:A10,">"&TODAY())
Count dates that are due in a week. =COUNTIF(A2:A10,"="&TODAY()+7)
Count dates in a specific date range. =COUNTIF(B2:B10, ">=6/7/2014")-COUNTIF(B2:B10, ">6/1/2014")

Here is an example of using such formulas on real data (at the moment of writing today was 25-Jun-2014):
COUNTIF formulas to count dates based on the current date.

Excel COUNTIF with multiple criteria

In fact, Excel COUNTIF function is not exactly purposed for counting cells based on multiple criteria. There is another function, COUNTIFS, especially designed for this (it is the topic of the next week's article). However, you can use COUNTIF to count values based on two or more criteria by adding or subtracting several COUNTIF functions.

Example 1. COUNTIF with two conditions for numbers

One of the most common applications of Excel COUNTIF function with 2 criteria is counting numbers within a specific range, i.e. less than X but greater than Y. For example, you can use the following formula to count cells in the range B2:B9 where a value is greater than 5 and less than 15.

A COUNTIF formula with two conditions for numbers

Example 2. COUNTIF with multiple conditions for text values

Let's consider one more example of the COUNTIF function that will count 2 different text values. Suppose you have a shopping list and you want to find out how many soft drinks are included. So, the following formula works a treat:


Please pay attention to the wildcard character (*) in the second criterion, it is used to count all kinds of juice on the list.
A COUNTIF function to count cells with 2 different text values.

In the same manner, you can write a COUNTIF formula with several conditions. Here is an example of the COUNTIF function with multiple conditions that counts lemonade, juice and ice cream:

=COUNTIF(B2:B13,"Lemonade") + COUNTIF(B2:B13,"*juice") + COUNTIF(B2:B13,"Ice cream")

You can find plenty more examples for text, numbers and dates in this tutorial - Excel COUNTIFS and COUNTIF with multiple criteria.

Using COUNTIF function to find duplicates and unique values

Another possible usage of the COUNTIF function in Excel is for finding duplicates in one column, between two columns, or in a row.

Example 1. Find and count duplicates in 1 column

For example, this simple formula =COUNTIF(B2:B10,B2)>1 will spot all duplicate entries in the range B2:B10 while another function =COUNTIF(B2:B10,TRUE) will tell you how many dupes are there:
COUNTIF formulas to find and count duplicates in 1 column

Example 2. Count duplicates between two columns

If you have two separate lists, say lists of names in columns B and C, and you want to know how many names appear in both columns, you can use Excel COUNTIF in combination with the SUMPRODUCT function to count duplicates:


We can even take a step further and count how many unique names there are in Column C, i.e. names that do NOT appear in Column B:

A COUNTIF formula to count unique values between 2 columns.

Tip. If you want to highlight duplicate cells or entire rows containing duplicate entries, you can create conditional formatting rules based on the COUNTIF formulas, as demonstrated in this tutorial - Excel conditional formatting formulas to highlight duplicates.

Example 3. Count duplicates and unique values in a row

If you want to count duplicates or unique values in a certain row rather than a column, use one of the below formulas. These formulas might be helpful, say, to analyze the lottery draw history.

Count duplicates in a row:


Count unique values in a row:

Excel COUNTIF formulas to count duplicates and unique values in a row

Excel COUNTIF - frequently asked questions and issues

I hope these examples have helped you to get a feel for the Excel COUNTIF function. If you've tried any of the above formulas on your data and were not able to get them to work or are having a problem with the formula you created, please look through the following 5 most common issues. There is a good chance that you will find the answer or a helpful tip there.

1. COUNTIF on a non-contiguous range of cells

Question: How can I use COUNTIF in Excel on a non-contiguous range or a selection of cells?

Answer: Excel COUNTIF does not work on non-adjacent ranges, nor does its syntax allow specifying several individual cells as the first parameter. Instead, you can use a combination of several COUNTIF functions:

Wrong: =COUNTIF(A2,B3,C4,">0")

Right: =COUNTIF(A2,">0") + COUNTIF(B3,">0") + COUNTIF(C4,">0")

An alternative way is using the INDIRECT function to create an array of ranges. For example, both of the below formulas produce the same result you see in the screenshot:


=COUNTIF($B2:$B8,0) + COUNTIF($C2:$C8,0)
A COUNTIF formula to count numbers in a non-contiguous range

2. Ampersand and quotes in COUNTIF formulas

Question: When do I need to use an ampersand in a COUNTIF formula?

Answer: This is probably the most arcane part. I personally find the syntax of the COUNTIF function very confusing, though if you give it some thought, you'll see the reasoning behind it. An ampersand and quotes are needed to construct a text string for the argument. So, you can adhere to these rules:

  • If you use a number or a cell reference in the exact match criteria, you need neither ampersand nor quotes, e.g. or =COUNTIF(A1:A10,10) or =COUNTIF(A1:A10,C1).
  • If your criteria includes text, wildcard character or some operator with a number, you enclose it in quotes, e.g. =COUNTIF(A2:A10,"lemons") or =COUNTIF(A2:A10,"*") or =COUNTIF(A2:A10,">5")
  • If you use an expression with a cell reference or another Excel function, you have to use the quotes ("") to start a text string and ampersand (&) to concatenate and finish the string off. For example, =COUNTIF(A2:A10,">"&D2) or =COUNTIF(A2:A10,"<="&TODAY()).

If you are in doubt whether an ampersand is needed or not, try out both ways. In most cases an ampersand works just fine, e.g. the formulas =COUNTIF(C2:C8,"<=5") and =COUNTIF(C2:C8,"<="&5) work equally well.

3. COUNTIF for formatted (color coded) cells

Question: How do I count cells by fill or font color rather than by values?

Answer: Regrettably, the syntax of the Excel COUNTIF function does not allow using formats as the condition. The only possible way to count or sum cells based on their color is using a macro, or more precisely an Excel User-Defined function. You can find the code working for cells colored manually as well as for conditionally formatted cells in this article - How to count, sum and filter cells by color in Excel.

4. #NAME? error in the COUNTIF formula

Issue: My COUNTIF formula throws a #NAME? error. How can I get it fixed?

Answer: Most likely, you have supplied an incorrect range to the formula. Please check out point 1 above.

5. Excel COUNTIF formula not working

Issue: My COUNTIF formula is not working! What have I done wrong?

Answer: If you have written a formula which is seemingly correct but it does not work or produces a wrong result, start by checking the most obvious things such as a range, conditions, cell references, use of ampersand and quotes.

Be very careful with using spaces in a COUNTIF formula. When creating one of the formulas for this article I was on the verge of pulling my hair out because the correct formula (I knew with certainty it was right!) wouldn't work. As it turned out, the problem was in a measly space somewhere in between, argh... For instance, look at this formula =COUNTIF(B2:B13," Lemonade"). At first sight, there is nothing wrong about it, except for an extra space after the opening quotation mark. Microsoft Excel will swallow the formula just fine without an error message, warning or any other indication, assuming you really want to count cells containing the word 'Lemonade' and a leading space.

If you use the COUNTIF function with multiple criteria, split the formula into several pieces and verify each function individually.

And this is all for today. In the next article, we will explore several ways to count cells in Excel with multiple conditions. Hope to see you next week and thanks for reading!

You may also be interested in:

556 Responses to "COUNTIF in Excel - count if not blank, greater than, duplicate or unique"

  1. Dustin says:

    I am trying to format a row of cells. Its to track requirements for scouts. If 1 of cells E6:E10 is an "A", and if all of cells E11:E14 are an "A", and if cells E16:E17 are an "A", and if E18 or E19 are "A", E20 will read "C", if cell between E6:E19 is "A" than E20 will read "P", or " " if none do. I have been trying for 3 days and I keep getting errors. Can you assist?

  2. Parminder says:

    can anyone tell me how to sort data in multiple sheets ?

    can it possible to sort multiple data same time in multiple sheet?

  3. john says:

    Hi Svetlana, I have a column A and a column B which can have a Y (short for Yes) entered into either of them for each row, but cannot have the Y entered in both in a row. The purpose is to record the number of, size A (140 litres)or Size B (240 litres).

    Once the size is recorded, I am also recording how full each of the containtes is. (100%, 75%, 50%, 25%) This is recorde with a simple Y within the relevant cells.

    At first i did not seperate the container sizes so i was able to use the ifcount function to total each column using the following formuala(=COUNTIF(E4:E12,"Y")

    Are you able to tell me how I can total the number of "Y" in each of the 100%, 75%, 50%, 25% colums but based on whether the container is size A (140 litres)or Size B (240 litres).

  4. GS says:

    Thanks for your tips!

  5. Atul Avasthi says:

    Hi Swetlana, Will you please help me to find the value for the below data:


    I want to count all unique values considering with duplicate as a one value.

    Hope you will help me.

  6. Xung says:

    Hi. I really appreciates everyone participation in this platform. Can any one help me with a formula how to calculated if an amount is 1000 and it's for 4 people makes it 4000 but if it's more then 4 like 5, 6 or 7 get it from 1050 and calculate.

  7. Ishan says:


    what formula can I use to put the following criteria in one cell and get the cell highlighted if any one of the criteria is met.

    a.) if criteria range of cell has more than 3 text
    b.) the sum of text in the criteria range is more than 5
    c.) if the text are togather in criteria range any blanks in them, it should be counted as ONLY one text and not multiple.
    d.) Everytime a text is entered in the range of cell, the cell should count 160 cells in reverse (backwards) to count the number of text from the current entered text coloumn and not count all the text in the criteria range.

  8. Ron says:

    I don't understand the example #1 countif with multiple criteria function. It says we want to count the numbers between 5 and 15. =COUNTIF(B2:B9,">5")-COUNTIF(B2:B9,">=15")

    If we are looking at the range that is greater than 5 and less than 15, why is there an equal sign on 15 if it is suppose to be less than 15?

  9. moses says:

    What is wrong with this formula? It produces a wrong value if zero.
    =COUNTIF(B2:B86,"Not Done")

    • Gods Servant says:

      sounds like cells have 0 values
      use this =COUNTIF($B$2:$B$86,"0")

      or use two formulas in different cells:
      1. =IF(COUNTIF($B$2:$B$86,"0"), "not done","done")
      2. use this =COUNTIF($B$2:$B$86,"0")

      IF THE Cells are blank then use this


  10. dave says:

    I need to count the number of names in a single cell (example B14).

    I don't want to do so by specific names since the worksheet needs to be applied to a large number of people. I have tried the formula =countif(B14,"*").

    However when I enter two names in cell B14 Smith Jones it only counts it has 1 instead of two.

    Does anyone know how to solve the issue.


    • Gods Servant says:

      This is for Dave...
      =IF(LEN(TRIM(B33))=0,0,LEN(TRIM(B33))-LEN(SUBSTITUTE(B33," ",""))+1)...
      hope it helps. if i understood your question correctly.

      change the cell reference in the above function to your liking.

      P.S. Great Post on COUNTIF...

  11. dave says:

    I need to know how to transfer statistics from individual excel worksheets located in a folder to excel workbook put on sharepoint. I also need to know how to get the statistical data from each individual excel worksheet to total for each day of the month.

    Thanks for your assistance.

  12. Rushikesh says:

    I would like to use countifs for Criteria_Range. My Criteria Ranges is also in between Dates Range. Can it is possible to use the Criteria Range in between along with Range in between particular Dates.
    eg. If there are two products named Apple and Ball updated in sheet for month of Jan. The said product sold in a month of Jan. Both the products are mentioned in A column. I would like to know how much Apples and Balls sold within particular Weeks for month of Jan.
    Kindly revert on mail.

  13. Jody says:

    I'm try to count the newest month with tips over 100 dollars and stop the count when the tip drop below 100:
    Column A July August Sept Oct Nov Dec
    Sara 125 94 121 82 120 135

    Working from Dec to July (newest to oldest month) the count would only be two, as the tips drop below 100 dollars in Oct....what formula can I use??

  14. Jamie says:


    I need som e help:

    How to remove Columns where values are missing in >50% of rows?

    Countblank didnt work for me. I have 30000rows, 300columns.


  15. Jody says:

    Hi Jamie,
    the example is two row of data: headers top row, data second row

  16. Steelskull says:

    Having difficulty writing a COUNTIF dynamic function that correctly counts a criteria based on the last 20 entries. Get confused using the OFFSET function, which I understand is essential for a dynamic database. Here's the formula I thought would work. Please advise. Thanks!


  17. Puru says:

    the question tells me to multiply a value and then add another value to it. How do I do this??

  18. Bruno says:

    I appreciate any help you can give me. I have a list of names down in a column.Each name has an associated value in the column to the right of these names.-----I copy a list of names from the internet and paste them along with their values to a blank portion of the spreadsheet.These names and values are in columns but they are separated by columns of other information and are three columns to the right.The Downloaded names in the column also include additional names that I do not need and the names that I download may be in a different order each time I copy the there a formula to pick out the values next to the downloaded pasted names for only the list of names that I require on my spreadsheet.I have tried many different formulas but do not seem to get them to work. thanks

  19. Kath says:

    Hi, i would like to know if there is a function for determining the specific number and location of spaces within a cell.
    For example,
    Cell A1 contains: X and cell B1 contains: X

    Thank you

  20. Bharat Jagtap says:

    I want to find the duplication in colomn.
    Tell me how to use countif formula.

  21. Ross says:

    Hi, I am setting up a sheet for bookings of a holiday home. I have date of entry and departure in 2 separate columns, I want to set this up so that when entering a new booking it will not allow duplicates or come up with a warning, can anyone help with what formula to use?

  22. Kyndra says:

    I am trying to count unique text in a column A3 - A186. Why doesn't this work? =SUMPRODUCT((COUNTIF(A3:A186,A3:A186)=1)*(A3:A186""))

    It looks like the same formula from above....

    I am using EXCEL 2010.

  23. Mohsen Ghasemi says:

    Hi Ablebite Team,

    For the "COUNTIF not blank" you wrote the formula as:
    It is good but for the cells which contains the formula and the result of formula is: "" (showing empty); by using above formula the counter will count them as not blank, but we do not want to count them. Please help what to do.


  24. David Harris says:

    I wish to count the numbers in a column where the number to be counted is less than the number one row below it. Is there a way to to this?

    Column F
    f1 contains the countif or sumproduct or whatever formula
    The range is F2:F72

    the values in F2 on are similar to:

    F2: 25
    F3: 19
    F4: 43
    F5: 14
    F6: 6
    F7: 73
    F8: 37
    F9: 41
    F10: 45

    The total in this case would be 4 (F3: 19, F6: 6, F8: 37 and F9: 41)

  25. Mohsen Ghasemi says:

    Hi Ablebite Team,

    For the "COUNTIF not blank" you wrote the formula as:
    It is good but for the cells which contains the formula and the result of formula is: "" (showing blank); by using above formula the counter will count them as not blank, but we do not want to count them. Please help how to count non blank cells excluding those cells.


  26. srujan says:

    i need a help regarding Countif as am keeping formula for one set of numbers which are duplicate.i need to get the count beside which is the newest like eg:12345 old one 1 should be beside

    12345 new one in same row i need 2 beside.

    12345 2
    5236 1
    52879 1
    12345 1 I need to get here 2

  27. Melissa says:

    How do I count multiple dates in one cell? Ex: Cell A1 contains: 2/19, 2/20, 2/21 I want cell B1 to count 3. I am using this formula =LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(B1),",",""))+1 but when I delete the dates in Cell A1 the number 1 appears in cell B1.

  28. reza says:

    I need to count cells with specific color .
    Is it possible?

  29. Armando says:

    cuantas veces puedo utilizar la palabra "countif" en una formula para sumar un mismo valor en diferentes columnas, total de columnas (6)
    ejemplo: =countif(b2:b100,"1/0")+countif(c2:c100,"1/0")..........
    y muchas gracias por la ayuda que me puedan prestar.

  30. fahad says:

    if you r there i want to ask something

  31. Dre says:

    Hi there...I have data that lists names of people and how many times they register per month. So Column A is a list of names and there could be multiple occurrences. If you are only supposed to register 2 times a month and i would like a way to identify by name those that registered more than 2 times a month and the frequency of times they registered. How do i do this?

  32. Areea says:


    i want to if 20% then show 4 if 21% to 30% then show 6 if 31% to 80 % show 8 if 81% to 100% then show 10

    how set this formula in excel please suggest me with example

  33. JGP says:

    Hi! my current formula is COUNTIF(a1,"PCS") my prob is, if it has another text in the formula, it doesnt count as 1. what formula should i use when i wanted it to count as 1 regardless if it has other containing text.

  34. JGP says:

    Hi! my current formula is COUNTIF(a1,"PCS") my prob is, if it has another text in the cell e.g PCS 1 jgp, it doesnt count as 1. what formula should i use when i wanted it to count as 1 regardless if it has other containing text for as long as it has PCS

  35. tany says:

    Hi!... well i want to know that if we have Date sheet of something issuance of inventory and now we want to sort that some one code or person how many times have issued the inventory how should we formulate this data.. actually i know the way of "Filter & the way of "Find" i m trying to formulate this data that i putt name or code in any specimen cell and got detail of all issuance times .. still confused and trying to find .. could you please have any idea
    thanks you .. tany

  36. Kate says:


    I have a spreadsheet with columns A and B. In column A is new results, in column B is old results. I need to count the number of occurances where the value in column A is greater than the corresponding value in the same row of column B.

    I have tried using
    =COUNTIF(A2:A100,">"&B2:B100)but it always returns 0 even though there are many instances of an A value being different than the corresponding B value

    I also tried =COUNTIF(A2:A100,">"&B2)But that returns the number of A values greater than B2 specifically instead of the corresponding B Value of the same row.

    Please Help!!!

  37. Gus says:

    I have 40 students, I wanna use COUNTIF to know how many students got from 7 to 7.9. I wrote it like this. +countif(a2:a41;">=7"&"<7.9") is it correct?
    Thanks in advance!

  38. Panos says:


    I have Col A containing "Problem Ticket Opening time", (e.g. 2017-04-05 19:20:04), Col B containing "Problem Ticket Closing time", (e.g. 2017-04-09 12:52:10) and Col C the Customer Name having the problem/ticket (e.g. Mr John Smith). Think of 3,000 rows and think that the same customer has repeated tickets. Sometimes tickets for the same customer repeat every few days (which i dont mind to count) BUT sometimes the customer (Mr Smith) calls today for a problem and calls AGAIN tomorrow (within 24hours).

    This is something i want to record within a month:

    E.G. Mr Smith created 5 tickets that were under 24hours gap.

    I am still trying to work out the logic. HINT which could be useful: I have managed to convert all times in Col A and B in Year Days (e.g. 31/3/2017 is day number 91).
    I am stuck...

  39. ashiv says:

    part number call out no. Qty
    nas 15
    nas 15
    nas 15
    pvb 25
    pvb 25
    tvs 35
    tvs 35
    xl 40
    xl 40
    xl 40
    I have callout number for each part. How to use countif formula in excel to count number of callout. Would like something like below.
    part number call out no. Qty
    nas 15 3
    nas 15
    nas 15
    pvb 25 2
    pvb 25
    tvs 35 2
    tvs 35
    xl 40 3
    xl 40
    xl 40

  40. Reza peiravi says:

    Dear World,
    Good Morning :)

    I have a question on countif formula ... as a example

    1. G-SS102C193-4E58-14-135
    2. TR036C216E53-18-140
    3. G-SS101RET-18E49-23-145

    i need to know the formula for count if there is " number -number "
    as example numver "1", in center of full txt there is "C193-4", i need the furmula to find cells with finding number-number formula .. if you see number"3" example. there is "word-number", i could fiqiur out to use search formula .. but i need furmula for counting or searching cells with " number-number ".

    Waiting for answer if anyone can help me please...

    Best Regards,
    Reza Peiravi.

  41. AURELIO says:

    Thank you so much. Incredibly helpful!

  42. Lucky Agarwal says:

    Please suggest formula -

    If in Column A I have names (Names are repeating) & In Column B Status against name like Yes & No.

    So If I want to count how many Yes are there against any particular name than how to do it. Pls help

  43. Dan Dewey says:

    I have a spreadsheet where I want to count yields on investments between various rates (i.e. >=1% and 1")-countif(p8:p29,">2").
    this returns 0.00%. Please advise. Thanks!!

  44. Ravi poudel says:

    I want to know formula to count cells that does not contain some values...more than one.

  45. CvH says:

    Good day all hope someone can help me
    ok here it goes hope it makes sense

    I need a line written for a worksheet of mine that does the following

    i have items that are being sold call it a orange
    then i have a date that it has been delivered
    now what i want to be able to do is have excel calculate how many of oranges how many apples how many grapes etc. there are 7 different items that i have still needs to be delivered based on a text insert "to be delivered"

    Thanks let me know hope this is enough info

  46. Frank Conway says:

    … Here is what I am trying to do:


    This is my current formula I use to determine how many units are processed for the day…Fortunately / Unfortunately I processed 87 installs yesterday from 3 ICSB locations so my formula doesn’t provide me with a quick answer to Marks daily question of “How many do we have done today” so I sit here and count them manually soooo I want to add to it:

    (D1179:D3396,”Carol Stream”)

    Basically I want a formula that I can utilize for each location that only counts the installs done for that location and not all installs done within a range that are dated for TODAY.

    If you can help it would be appreciated!!

    Thanks 

  47. Yusuf Kuris says:

    Thank you!

  48. andy ignacio says:

    When are you gonna add windows 11

  49. Bill Hunter says:

    Greetings, Svetlana!

    First let me tell you how impressed I am with the clarity of your explanations. Your written English is more lucid than that of most native speakers— seriously! My hat’s off!

    Let me try to explain what I want to do here, and please bear in mind that I’m a novice.

    I’m in the midst of trying to create a calculator in Excel (2010) that will enable our sales support personnel in a large financial services brokerage agency to efficiently and accurately return guaranteed/non-guaranteed lifetime annual income results from our brokered line of fixed indexed annuity products, given varied investment input, and each product’s individual growth and payout parameters. This would allow us to give very accurate and definitive advice to our contracted agents about which annuity contracts would be most appropriate for given clients.

    The four unwieldy IF/AND statements which are copied and pasted below result from each of 4 possible value output combinations obtained from two binary decision points (which are interfaced with two independent groups of two form control radio buttons). I have tested each of the four IF/AND statements separately and they return correct output.







    Let me add that each successive statement after the first simply reduces each of the whole series of nested IF outputs by .05.

    Here are my two questions with regard to all the foregoing:

    1) How do I integrate the four IF/AND statements into a single statement that will correctly function in concert with the radio buttons and return a single correct output?

    2) Does Excel provide some function or other way of reducing the complexity of the nested IF statements?

    If you can help me with those questions, I’d be much obliged.

    With kind regards,
    Bill Hunter

    • Hello Bill,

      Thank you very much for your kind words! For me to understand the task better, please reply to my email with a small sample of your data, formulas and expected results. (I've removed your email address from this blog for security reasons).

  50. Nebojsa Tatic says:

    Is it possible to use countif function in whitch criteria is text in comment attached to the cell?

  51. Giovanni Ciriani says:

    Does Countif work with Frequency? I've found an example to count how many different values there are in a large array as the following
    = SUM( --(FREQUENCY(A1:A1000,A1:A1000)>0))
    so I thought that COUNTIF should work to
    = COUNTIF(FREQUENCY(A1:A1000,A1:A1000),">0")
    However, the formula is not accepted. Do you know if there is a limitation in the type of argument?

  52. Eric says:


    Please I want to group items but want to write a function so that all those who qualify are assigned 1 and those who do not qualify are assigned 0. Please help

  53. Ling says:


    I am trying to write a formula to count the number of calls for the first meeting from two separate spreadsheets. I was able to count the total calls for all the meeting but I can figure out how many calls for the first meeting and 2nd meeting and so on.

    My first Spreadsheet is the meeting sheet and I have customer column, created date. the second sheet is the phone call sheet with customer name and the date (each call create one date)

    I used countif formula to count if the customer exist in the phone list then count the number of call but this formula only gave me the total then I added additional count if formula to say if the phone call date less than the meeting created date then count those number but all I got an error value. Can you help?

    Thank you,

  54. Calin says:

    Hi there , I have a simple table I did not set it up as pivot table. First row are dates and under each date there are columns with stock symbols( 10-50 symbols under each date average ). I am using a count if formula in conditional formatting to determine how many times each stock symbol appears in my table =COUNTIF($A$2:$Z$100,A2)=5 it is a relative reference and I ask a certain color for cells that meet criteria . I set up the range to be same as in formula A2:Z100 Every day I add up symbols and some times I delete columns that are dated more than 2 months ago. The problem is that every day after I add up a new column the range that formula apply to is changing so I have to manually set it up ( conditional formatting/manage rule /this worksheet/applies to )
    How can I overcome this problem ? Basically I was looking to add up every day a column and symbols that meet criteria of countif formula to be highlighted let say green

  55. Achira says:

    I want to know from you that how can I count the two cell data at a time like:
    OC TC
    OC SC
    OC SC
    OC EC
    OC TC
    OC ST
    R1 SC
    R1 TC
    R1 SC
    R1 TC

    I want to know for OC how many TC of SC and for R1 how many TC or SC Like ------------



  56. Chris says:

    Hello Svetlana,

    I have been reading through your website and can't see the answer to what I thought would be a fairly common question. Can you please help?

    I have a created a spreadsheet of people from 10 different work teams. The team names are in column E. Columns J through AG are a range of competencies people are required to complete.

    For each person’s row, where they have completed the competencies matching the column heading I would like to record the date they completed that particular competency.

    For example:
    . A B C D
    1 Name Team Competency 1 Competency 2
    2 John Red 10/08/17 16/07/17
    3 James Red 16/07/17
    4 Bob Red 16/07/17
    5 Sara Blue 16/07/17
    6 Gale Blue 11/07/17
    7 Alice Blue
    8 Dave Blue 16/07/17
    9 Jane Green 11/07/17
    10 Blair Green

    What I cannot work out is how to create a formula to count the number of people from the Blue team (or any specific team) who have completed Competency 1.

    Ideally the results of these formulas will be used to create a table similar to this:
    Team Com 1 completed Com 2 completed
    Red 2 2
    Blue 3
    Green 1

    Thank you for your assistance.

  57. SAMMIE says:

    HI THERE ,


  58. Kim says:

    I am trying to count certain text in a range of cells from another tab. I need to count "status" and "status date" as separate formulas for counts. I tried this but didn't work: =COUNTIF('690-JHC3 Ven 600-699'!F4:F10001,"*STATUS*")and the other =COUNTIF('690-JHC3 Ven 600-699'!F4:F10001,"*STATUS DATE*")

    Appreciate the help - thanks

    • Neal Kelly says:

      Hi Kim,
      Try this
      =COUNTIF('690-JHC3 Ven 600-699'!F4:F10001,"*"&"STATUS"&"*")

      This should ensure that STATUS is counted no matter what is before or after the word.

  59. Lucas says:


    I am trying to use COUNTIF to count the output of a formula, e.g. a cell that contains a formula. The formula is a nested =IF() function.

    COUNTIF however doesn't seem to see the output. For example, if other cells meet certain criteria, the output is "D". When I then use COUNTIF to count the "D"s it returns 0.

    I'm familiar with COUNTIFS but I am using COUNTIF in this way to measure time and create a Gantt chart that doesn't overlap processes. If one process is going on, the cell returns "D" (for delay) and I can count that delay, via COUNTIF.

    So, can I count formula outputs via COUNTIF, or is there another way to count formula outputs and/or display them differently?



    • Hello, Lucas,

      COUNTIF should work in your case perfectly, I'm afraid the problem lies in something else. Perhaps, it is the spaces around "D". We could suggest you better, if you sent us your file with the formula to Don't forget to include the link to this comment in your email.
      Please don't worry if you have confidential information in your document, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.

  60. Scott says:

    Hi, Please see the following chart.

    A B C D E

    HOUSE 4 4 - HOUSE 4 4 - HOUSE
    HOUSE 4 4 - HOUSE 3 - BOX
    TOY 1 1 - TOY 1 2 - SHOE
    HOUSE 4 4 - HOUSE 1 - TOY
    SHOE 2 2 - SHOE 2
    SHOE 2 2 - SHOE
    SOCK 1 1 - SOCK
    HOUSE 4 4 - HOUSE
    BOX 3 3 - BOX 3
    BOX 3 3 - BOX
    BOX 3 3 - BOX

    Column A is the item, column B is the count for each reoccurrence in column A, column C is =CONCATENATE(B1," - ",A1), column D counts and removes duplicates =IF(COUNTIF($B$1:B1,B1)=1,B1,"") and column E sorts and shows how many of each. The problem I am having is it only seems to count an item with a count of "1", only once so if there is more than 1 item with one occurrence, it only counts the first one. Can you tell me what I'm doing wrong?

    My formulas are as follows:
    Column A (no formula)
    Column B =COUNTIF($A$1:$A$11,A1)
    Column C =CONCATENATE(B1," - ",A1)
    Column D =IF(COUNTIF($B$1:B1,B1)=1,B1,"")
    Column E =IF(ROW(A1)>COUNT($D$1:$D$11),"",INDEX($C$1:$C$11,MATCH(LARGE($D$1:$D$11,ROW(A1)),$D$1:$D$11,0)))



  61. Marissa says:

    I realize this post is old and I might not get a response now, but I figure it's worth a shot since the original article helped me a lot.

    I had started with the formula

    =SUMPRODUCT((COUNTIF(F12:V2015,'Actividad (2) de LiCS'!C15:C2020)>0)*('Actividad (2) de LiCS'!C15:C2020""))

    to count the number of unique names in a list that also occur in a list on another worksheet. I also wanted to know the number of such names associated with a particular date range, so I edited to

    =SUMPRODUCT((COUNTIFS(D12:D2015,">"&(DATE(D10,1,1)),D12:D2015,"0)*('Actividad (2) de LiCS'!C15:C2020""))

    and it worked great. But then I realized I also wanted to count the total number of names - even repeats - that occurred in both lists. I used the formula

    {=SUMPRODUCT((COUNTIF('Actividad (2) de LiCS'!C15:C2020,F12:F2020)>0)*(F12:F2020""))}

    to do so, and also worked great. But when I try to make similar "countifs" edits to specify date ranges, it returns a #VALUE! error. The edited formula I'm getting the error on is


    Any advice?

    Thanks in advance!

  62. Sam Korel says:

    I am trying to count the numbers that not equal in one column. for example


    I am using count if but it's not working.

  63. Aqua93 says:

    Using countif i want count time 25:00:00 to 35:00:00. can tell me the formula to solve this???

  64. William says:

    How do I create a formula that can work as a criteria range?
    Here is an example.


  65. Rich says:

    Attempting to use Countif to determine month, however data range has date stamps

    data example:

    Row A1 "01/11/2017 08:25:44"
    Row A2 "31/10/2017 12:42:16"

    attempted countif:
    =COUNTIF(A1:A2,"nov") produces no result???
    please advise

  66. Maria Pessoa says:


    Gostaria fazer a contagem de células preenchidas (AI:AW), porém apenas das linhas indicadas por um critério (coluna I). É possível?

    Muito obrigada!

    • Hello, Maria Pessoa,

      Please try to use one of the following formulas:




      Hope it will help you.

  67. Eric says:


    I have a problem to find the proper formula for a sheet that trying to create.

    In column A, I want the user to type the code of a customer.
    That i have sorted with a data validation where only one entry is allowed.
    However, only one entry per day. So the next day, the code should again be used.

    CODE Date TIME
    100 18/11/2017 14:55:36
    100 18/11/2017 14:55:36

    As you can see here, the code 100 can be used twice but that is what i would like to avoid.
    The code 100 can only be used 1 time per day.
    The sheet is for marketing purpose where the transportation expenses of a visiting customer is paid back. But only one time per day of course.

    Is there a way, if possible with a data validation where i can enter a code to have a validation based upon a one time per day entry ?

    Or one time 100 per day entry but the following day 100 is again usable but also then only 1 time.

    I have been struggling with this issue now for 2 days and i simply can not find it.

    Thanks ever so much for the support.


  68. Kendall says:


    I have made a few unsuccessful attempts at making a formula to count pairs of cells based on specific criteria. I need to make a formula that will count how many pairs of cells meet the criteria. For example, if A1=2 and A34=1, then it will count that as 1 pair. Upwards of 12 pairs of cells will need to be accounted for, and I cannot select a range of cells because cell pairs are far apart.

    Thanks for your help

    • Hello,

      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  69. Steven Greidanus says:

    Hello, I want to count qualifications. Column A has the number of days a qualification is good for, Column B has the date the qualification was completed. I want to take today's date - date qualification completed and count if it is less than the day value in Column B. Can you help?

    • Hello,

      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.

      I'll look into your task and try to help.

  70. Alex.onugha says:

    81150001% is not available at the moment 💡

  71. Bilal Memon says:

    Need Help

    1 2 3 4
    Alpha 1 Alpha 3 ? ? ?
    Beta 1 Beta ? ? ? ?
    Gama 1 Gama ? ? ? ?
    Beta 2
    Gama 3
    Gama 4 Formullae required How "3" Comes In Alpha 1
    Alpha 1
    Beta 2
    Gama 1
    Beta 4
    Gama 4
    Beta 3
    Beta 4
    Gama 1
    Gama 2
    Alpha 1
    Beta 1

    • Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  72. Brian says:

    I would like to count non-empty cells, but using the count if formula, it also counts the empty ones because they have a formula that return a value of nothing/blank/empty. How to make excel not to count these formulated empty cells?

  73. Iuliia says:

    could you please help to fix my formula? something is not working here
    where i have a table for date in text format (.08.), and another column where added some text. basically i need count how many cases in column K from the 8th month

    • Hello,

      Actually, your formula for is correct, if it doesn't work, most likely there's something wrong in your table.
      If you still require our assistance, please send us your workbook with the data and the result you expect to get to Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Don't forget to include the link to this comment into your email.

      We'll look into your task and try to help.

  74. Kevin says:


    If I want to count or sum a COLUMN, I can do =sum(F:F) for column F

    Let's say cell A1 has F in it,

    How can I reference A1 to do sum (so I can change that cell to do different columns like G, H, I, J).

    I tried like =sum(&A1&:&A1&) but that doesn't help - I was hoping that would calculate as = sum(F:F)


  75. moh says:

    i want count for example how many (+) in an excel function in a cell
    so the count will be 3
    is there a a function for that ?
    or to count how many characters will be calculated
    for the same example it will be 4

  76. JOYCE PASLEY says:

    I'm trying to set formula to keep track of wire by feet. B2 does not have a figure but will have one and B2 will change as wire is depleted. I want B2 to subtract entries in cells B3:B27 and show the balance of feet on hand in B2. I've put in formulas for subtracting but I can't put a formula in B2 while keeping the balance of wire in that same cell B2. HELP!

  77. Steve Roesch says:

    I have 2 columns of data. Column A contains a date. Column B contains a temperature. Temperature measurements are made several times each day, so several rows will have the same date. I'm trying to determine how many unique days a specific temperature is reached. So, if a temperature is reached several times during the same day, it should only count as "1" occurrence.

  78. ASHRAF says:

    how can i use wild cards for numbers

  79. Leesh says:

    HELP PLS! I have a list of dates in Coulmn L and then a list of dates in Cloumn M. I want to be able calculate network days between L:L and M:M and if the difference is 3 don’t count. I then want to sum all the 1’s! I would like to do this all in one formula and without a helper column if possible. I have over the 1000 dates in column L and the same in coulmn M. Is this possible?

  80. Albert says:

    Please help with my formula. I'm trying to get a total number in a cell based on text in a cell range (2 different parameters). My formula is as follows: =IF(COUNTIF(G3:G30, "Complete") < 1,(G3:G30, "Terminated") < 1,0, COUNTIF(G3:G30, "Complete")(G3:G30, "Terminated")) It is not working correctly. Any idea what I'm doing wrong? Thank you.

  81. Lee says:

    Helllo - Please can you assist.
    I am currently using the following formula and when there is nothing to display it shows a 0 how do I change the formula to show blank instead of 0

    =COUNTIF(Table1113[@[CCN1]:[Plant & Appliances in Non Domestic Premises]],">"&TODAY())

    many thanks

Post a comment

Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools -
Ultimate Suite for Excel Professionals
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Sheila Blanchard