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

Similarly, COUNTIF formulas work for numbers. As shown 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 and with the "" criteria to count all empty cells.

Formula to count cells not containing any text:


Since an asterisk (*) matches any sequence of text characters, the formula counts cells not equal to *, i.e. not containing any text in the specified range.

Universal COUNTIF formula for blanks (all value types):


The above formula correctly handles numbers, dates and text values. For example, here's how you can get the number of empty cells in the range C2:C11:


Please be aware that Microsoft Excel has another function for counting blank cells, COUNTBLANK. For instance, the following 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 and COUNTBLANK count cells with empty strings that only look blank. If you do not want to treat such cells as blanks, use this formula instead:


For more information about counting blanks and not blanks in Excel, please see:

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/1/2014")-COUNTIF(B2:B10, ">6/7/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 designed to count cells with multiple criteria. In most cases, you'd use its plural counterpart, the COUNTIFS function to count cells that match two or more criteria (AND logic). However, some tasks can be solved by combining two or more COUNTIF functions in one formula.

COUNTIF - COUNTIF to count numbers within a range

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

COUNTIF + COUNTIF to count cells with multiple OR criteria

In situations when you want to get several different items in a range, add 2 or more COUNTIF functions together. Supposing, you have a shopping list and you want to find out how many soft drinks are included. To have it done, use a formula similar to this:


Please pay attention that we've included 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 formula with multiple OR conditions that counts lemonade, juice and ice cream:

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

For other ways to count cells with OR logic, please see this tutorial: Excel COUNTIF and COUNTIFS with OR conditions.

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: It is probably the trickiest part of the COUNTIF function, which I personally find 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. For example:


If your criteria includes text, wildcard character or logical operator with a number, enclose it in quotes. For example:

=COUNTIF(A2:A10,"*") or =COUNTIF(A2:A10,">5")

In case your criteria is 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:


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. both of the below formulas 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!

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

  1. Vani says:


    I have Year in Column A (from 2005-2012), names of countries in Column B (like India, Australia, England, South Africa, etc.) and whether they "Win" or "Loss" in Column C. How do I use the Countif function to determine how many times India Won in total?

  2. Erik says:

    I have squardon in cell B.. I have training Status in Cell G. I am trying to make a formula that stats If cell B is (certain squadron) and Cell G is Overdue than put number in Cell if 10 civil engineers are overdue for training.. Cell k should say 10..? of it medical squadron has 7 people over due. it will pick just those medical squadron people out as overdue.

    Goal is to have multiple squadrons on one sheet and with current and overdue as training status and then break it down between squadrons on amount of people current and overdue.

  3. Muhammad Ahmed says:

    Nicely Explained!
    Suppose I have a Columns which contain some text and numbers including some errors also like (#N/A,#VALUE etc). How to count only text and numbers cell not including any error in it.

    Thanks in Advanced. :)

    • Doug says:

      I think this formula is what you're looking for:
      I used the range A65:A76 for my test, but you can replace this with whatever range suits you.

  4. deso says:

    What i need is,

    i need different article numbers to be counted in a list but if it has 0 on stock than i dont what it to be counted.

    looking forward !

  5. EY says:

    Hi Ablebit team.
    I am trying to count the number of cells in a column which contain a 6 digit number that starts with 5, and the third and fourth digits of that number are 60 (for example, 52601, 53607, etc.).
    I have tried =COUNTIF(C:C,"5?60??") and it always calculates the answer as zero.
    I have also troubleshooted with other wildcards ("5*", "5?????"), etc and every time it answers zero.
    The only way I can get a real value is if I set the criteria as a full 6 digit number (for example, "516000")
    Am I using the wild card incorrectly? Please help! Thank you.

    • Andrew Kingdom says:

      Hi EY,
      This is a deep problem with how Excel is storing the numbers you are searching. In using those wildcards, Excel tries to match text values, not number values, as Svetlana alluded to above. There are several fixes, none of them ideal:
      1. Change the cell format from General to Text. This will cause problems if you ever try to enter a formula in those Text formatted cells.
      2. Insert a new column D, with a function to convert the number to text, e.g. cell D1: =Text(C1,"#") and copy that formula for the rest of column D down to the last number you have in column C. If column C is already a formula, wrap your formula inside the Text function, e.g. cell C1: =Text(Sum(A1,B1),"#")
      3. Insert a new column D and do the matching using text functions. e.g. cell D1: =AND(LEN(C1)=6,MID(C1,1,1)="5",MID(C1,3,2)="60"). This checks that length is 6, the first character is 5 and characters 3 to 4 are 60; you can then do a count of cells with a TRUE result, e.g. =COUNTIF(D:D,TRUE)

  6. A Rajesh says:

    if the number is 15 digits (37AAYYCC0866A1Z) its ok less then 15 digits number is not Ok give me the formula

    • Philip Hinton says:

      A Rajesh, assuming your first number is in A1, then use LEN(A1)=15. This will give you TRUE or FALSE values.
      If you must have "OK"/"Not OK" then use if(LEN(A1)=15,"OK","Not OK").

  7. jyoti says:

    i have two column and i want common values from both column should be highlighted in one column. i doing this manually so it works but not working this formula in code. formula is=COUNTIF(H1:H40,D1:D136). i am using same in code but its not working

  8. Tou Moua says:

    A B
    123 OMIT
    456 OMIT

    Count = 4

    How do I get B10 = 4 (do not count column A when there is "omit" on column B)

    Please help and thank very much.

  9. Tou Moua says:

    The previous post didn't look good. Sorry!

    Thanks again,

  10. Rob says:


    I'm trying to create spreadsheet that calculates student scores.

    Students get graded as either a 1, 2 or 3 which goes in a column next to their name - pretty standard.

    What i'm having trouble with is trying to count the number of students who got a grade 2 but who also tick another criteria, which is that they have English as an additional language (EAL). I have a column next to their name which simply has a Y in it if English is their additional language. So i'm tring to count: "How may students who have EAL got a grade 2?"

    I managed to get the count for those with grade 1, and 3 but when trying to apply the same formula to the 2's, i get the error message: "ErrorFunction IF parameter 1 expects boolean values. But 'Y' is a text and cannot be coerced to a boolean."

    This is the formula i used to successfully count the students with EAL that got 1's and 3's (the initial data is on a different tab called Y6):

    I get the error message above when i use the same formula for the 2's

    Any idea how i can calculate these totals?


  11. Urvil Dajri says:

    Thank you for sharing this information

  12. reaz says:

    there is lots of number 0 to 2000. i want to count how many numbers are there between 0 to 100. For that i use =COUNTIF(A1:A20,"<100"), then what is the formula i need to use to count 101 to 2000?

    • Christopher Mumbere says:

      Since you want numbers btn 0 and 100, it implies 0 & 100 will be left out in the formula, so the best way is to use a multiple function as;

    • Philip Hinton says:

      reaz, assuming that you want to include 101 and 2000 in your count, then use =COUNTIFS(A1:A20,">100",A1:A20,"101",A1:A20,"<2000").
      Please note that your original formula =COUNTIF(A1:A20,"<100") will include any zeros, so your count will be 100, not 99

  13. Mitch says:

    Thank you so much for this!

  14. PRADEEP says:

    CB/00004 FROCK GIRLS
    CB/00010 FROCK GIRLS
    CB/00011 FROCK GIRLS
    CB/00011 T-SHIRT
    CB/00015 T-SHIRT
    CB/00017 T-SHIRT
    CB/00017 T-SHIRT
    CB/00017 CAPRI
    CB/00017 CAPRI
    CB/00018 CAPRI


    FROCK 3
    T-SHIRT 4
    CAPRI 3

    • Nikki says:

      Hi! I am learning Excel in more depth myself and have found a way for you to display your desired result, even after adding more bill numbers and item names. In my formulas, the bill number column is not included; only the item name column and headers which I have created for formula reference. This is great, because the formulas will automatically calculate and either increase if more data is entered in column C or decrease if data is deleted in column C. However, the item names must be the same. If not, you can always add more headers and use the formulas as a reference for new items.

      Let's assume your bill number and item name column headers are in cell B2 and C2, respectively. In reference to the data you listed, your bill number data is in column cells B3:B12 and your item name data is in column cells C3:C12.

      There are 4 headers to the right of columns B and C. Cell E2: Item Count, Cell F2: Frock Girls, Cell G2: T-Shirt, Cell H2: Capri.

      In cell F3, the formula for calculating the total number of Frock Girls is: =COUNTIF(C:C,"Frock Girls") - the result should be 3. In cell G3, the formula for calculating the total number of T-Shirts is: =COUNTIF(C:C,"T-Shirt") - the result should be 4. In cell H3, the formula for calculating the total number of Capris is: =COUNTIF(C:C,"Capri") - the result should be 3. Now, for the final result!

      Remember, the Item Count header is in cell E2. In cell E3, the formula for combining the information for Frock Girls is: =CONCATENATE(LEFT($F$2,5)," ",$F$3) - the result should be "Frock 3". In cell E4, the formula for combining the information for T-Shirts is: =CONCATENATE(LEFT($G$2,7)," ",$G$3) - the result should be "T-Shirt 4". In cell E5, the formula for combining the information for Capris is: =CONCATENATE(LEFT($H$2,5)," ",$H$3) - the result should be Capri 3.

      I hope you find this useful. :)

  15. Robynne says:


    I am trying to count if a student's score is lower than the total points possible. Each column has the score listed in row 4, the category listed in row 3, and, in the case of the first student I am working with, the student's scores in row 5. Each column is a different day, from column D through AT.

    Right now, I have the following formula, and am getting 0 returned even though the student has partial credit on several assignments.

    =COUNTIFS(D5:AT5, "<" &D$4:AT$4, $D$3:$AT$3, "HW")

    Is it just too much to ask to compare a range of cells to a corresponding range of cells or am I making a mistake here?

  16. pratap singh says:

    Thank you for sharing this information

  17. Ken Tan says:

    I am trying to get the highest value from a list of serial number range list

    i am looking for the max value by searching only the rightmost 12 letter in range.

    Sample data
    000112717423 - 000112783422
    000112783423 - 000112837322
    000112837323 - 000112811822
    000112811823 - 000112812322
    000112811823 - 000112812322

    expecting answer

    because i am using excel 2010, i cannot use the following code

    • Andrew Kingdom says:

      Hi Ken,
      This is most easily solved if you are able to insert a separate column as an intermediate step to get the value. e.g. cell L2: =Value(Right(K2,12)). This can be hidden but you need to there are enough formulas if you add new data.
      Andrew K.

  18. Josh Vanhee says:

    Can i use the countif function to do the following: in a range of rows,i want to count the rows and then select only those rows where the value in column B differs from the value in column A.

  19. XP says:


    If i want to count the number of cells in the range B2:B10 with a date greater than or equal to the date in another range (let's say Z2:Z10). How can i change the following formula:



    • ZeroRowArray says:


      Provided all the cells are formatted for date your formula should work...if you take the 7 out of those


  20. Phillip says:

    I have this in several rows of column AE


    and I want count the YES, several results are YES, so


    but results=0, please help

  21. NK says:

    I have many cells where I used " countif " formula. I need to sum all these cells .When I use the Sum fucntion or I add the cell using "+" they result is always zero.

    Whoever, When I select the cells , it gives me the right total on the bottom of the excel window.

    How can I solve this?


  22. AMIT says:

    IF 3971 * 1.75 % = 69.4925 THAN WE REQUIRED HERE 70

  23. Phill says:

    I am using the COUNTIF function to look at a number of date cells and count the number that are overdue. If i type the formula =COUNTIF(E2:E6,"<1/12/18") it returns the number 3 which is correct. I want the formula to use todays date automatically but substituting 1/12/18 with TODAY() returns zero. Have also tried substituting 1/12/18 with a cell (B12) but again 0 returned. Any ideas?

  24. Doug says:

    Have you tried putting the < in double quotes? Like this:

  25. Deanna says:

    I am trying to write a formula for a row. I want to exclude blank cells as well as cells with dates in them. Every formula I have tried, I end up with a date for an count.
    Please help, and thank you!

  26. Ganesh says:

    I have a list of dates, want to count the number of people present in each month.
    Can someone help me out

  27. Maqbul Basha M.S says:

    =COUNTIFS(F2:F255,"FTD - Operations")=COUNTIFS(G2:G255,">="&E258,G2:G255,"="&E258,G2:G255,"<"&EDATE(E258,1)) IT COUNT THE DATE REQUIRED


  28. mohammad shadab says:

    i want to know how to count zone wise Serial no on Excel file

    Zone count of Serial No

    Zone Serial No
    North 100972
    North 100973
    West 100974
    East 100975

  29. Dennis Huff says:

    I have an array (say I5:Y5) with each cell holding a string (say 1-2-0, 3-0-0, 1-1-1, 0-2-1, etc). I want to count the number of cells in the array where the first number in the string is greater than the second number in the string. I have tried:

    =COUNTIF(I5:Y5,(LEFT(I5:Y5,1)>(MID(I5:Y5,3,1))) with no luck and have tried various forms (such as using quotes around the >, quotes around the formula, etc.), still no luck. It seems there is no COUNTIF formula where I can count when the first number is greater than the second number in the string. Can this be done, either via editing this formula or by VBA code?

  30. Anand says:

    i want to countifs only the cell value with dates in a range of cells with numbers, texts and dates
    for example


    countifs only cell value with dates

  31. Jovi says:

    i have shift roaster and the shifts are marked as A1, A2, B3 etc.. so i want to count the number of guys coming in A1 shift, A2 shift like that let me know how can I count this.

    Emp1 A1
    Emp2 A2
    Emp3 B3
    Emp4 B3

  32. Thomas Johnson says:

    i want to count the number of cells in one row that are less than corresponding cells in another row.

    I tried this formula; =COUNTIF(B12:j12,"<"&B$15:j$15) and it does not count.

    Row 12 is: 5 5 5 6 6 7 4 5 6
    Row 13 is: 5 5 6 6 7 7 5 5 6

    The count should be three.

  33. Neelakanta says:

    Thank you very much for sharing the information.. very useful

  34. Confused says:

    I'm trying to learn more about COUNTIF formula with multiple OR conditions. In that section in the article above, it gives two examples how to accomplish this: +SUM(COUNTIF(INDIRECT({"B2:B8","D2:C8"}),"=0")) and
    =COUNTIF($B2:$B8,0) + COUNTIF($C2:$C8,0) ) It then states they both return the same result. I understand that. When, however, one references the given photo of the data set, shouldn't the explanation actually read +SUM(COUNTIF(INDIRECT({"C2:C8","E2:E8"}),"=0")) and
    =COUNTIF($C2:$C8,0) + COUNTIF($E2:$E8,0) ) NOTE my change from "B" to "C" in the first range and "C" to "E" in the second range. The formula bar in the data set photo seems to support this.
    If it is actually correct as written in the *explanation, could you please help me understand why? WITH MANY THANKS !

  35. Richard S says:

    Trying to use the COUNTIF to report the number of instances in the combination of two columns. I want to count when Col P contains "A" and Col L contains "No email". Cannot seem to get this right.

  36. Praakash N says:

    This formula works fine only up to 10 cells if increase the range gives #Div/0! Error can some please help in this data is like this in column A,G,H I have data in column H I have unspecified repeated data need to count this with multiple cretaria

  37. Cherie D says:

    I'm trying to count the number of times a date between 1-Jan and 31-Jan is entered into a column. The text in the column is formatted as 1/1/2019 which displays as 1-Jan. I've tried the formula:
    and I've tried
    and I keep getting zero as the answer when the answer should be 22.

  38. SOUD SALIM says:

    Dear Sir,

    Could You please provide the formula regarding below mentioned data.

    DRIVER Trips 01-Jan 02-Jan 03-Jan 04-Jan 05-Jan 06-Jan 07-Jan
    157 62
    213 63
    223 52
    270 58
    393 48
    400 51
    410 74
    415 39
    433 51
    450 51
    495 31
    The above mentioned details for the month. we need to category like date ways from master sheet.

    An early response much be appreciated.

  39. K1 says:

    hello can you please help me i have a sheet with date delivery to the customer and im trying to use countif formola to have date more than X date but its not working and error all the time what should i do ? thanks

  40. Adeniyi says:

    Please i want to count between a range using = and less than but its not giving me the right answer. This is it: =COUNTIF(H8:Z8,"=12")-COUNTIF(H8:Z8,"<16"), with this formular, its giving me -15.0, whereas if i count manually its 16. but when i use =COUNTIF(H8:Z8,"=12")+COUNTIF(H8:Z8,"<16")(changing the minus sign inbtween the two countifs to plus) i get 19. Please help with this.

  41. Prit says:

    Thank you so much!!

  42. Nicole says:

    I was looking for a COUNTIF formula that would count non-blank cells that include both text and numbers. Your tip saved me a ton of time! Thank you.

  43. Alejandro says:

    I’m trying to figure out how to replace the string for the condition to a variable. As in:

    COUNTIF($A$1:$A100, (text in cell to the left))

    How would I do this?

  44. Akram says:

    I would be grateful if someone can help me. I have a list of board directors and the companies they are directors in.
    My Excel data list is quite large, and here is an example of how it look like.
    Name of director Companies
    A Company 1 Company 4
    B Company 1 Company 2 Company 3
    C Company 4

    I need to convert it to a matrix table like this
    Company 1 Company 2 Company 3 Company 4
    A 1 0 0 1
    B 1 1 1 0
    C 0 0 0 1

    Any ideas, how can I Do this.

  45. P.B. Nunery says:

    I'm trying to count the number of times a unique word is used in a single cell, i.e. =IFCOUNT(A1:A1,"thus")
    Looking for over use of a word. Cell A1 contains a sentence. All my results come out zero (0) even though I can plainly see that the word is in the sentence 3 times (all lowercase).

  46. zIZI says:

    Race is on column D
    Gender is on E
    Age is on F
    Post levels on G
    Age on K

  47. Jee says:

    Hello I have a spreadsheet that contains list of applicants, datetime applied(datetime) and on the other sheet date(date), total applicants. Im trying to count all the applicants within that date by counting the applicants with specific date comparing on the date(date) on the second sheet. my problem is how to convert range into int so that I con compare date and datetime. COUNTIF(INT('Applicants'!$B$2:$B$13), INT(A2)). Can anyone help me with this problem

  48. Zola says:

    Thank you very much, this is helpful.

  49. 1Roso says:

    this counts also cells containing FORMULAS!
    For instance if I have a formula that produces either a number or empty cell (based on some calculation) the above countif formula will count even the empty ones.
    I had to use the =COUNTIF(range,""&"*") formula as I had only number or EMPTY cells. Dont's know however if also date and other data types would be outcome of calculating formula, how this would end?

  50. Ahmed says:

    Thank you... this was driving me crazy!!! THANK YOU SOO MUCH

  51. Lynne says:

    I want to count blank cells in a range, and exclude any cells from the count that have spaces in them. (Where the cell appears to be blank, but actually contains a space)

  52. Asukwo says:

    I've a data set containing number of reported illnesses (1 for the illness, 0 for no illness). I want to count the number of illnesses per sex (male and female) without repetition, meaning once a male household has malaria, all other male households have it. How do I do this?

  53. Mike says:

    track contact attempts per person with the dates and times with a space in between that date and the time in each cell. I want a cell to total all my contact attempts for today. I tried countif with my criteria as today(), but that's not working since I have times in the cells also. What should I put for the criteria so countif will total up all the cells containing todays date along with any time? Thank you.

  54. Angela says:

    I have a range of numbers. I want to do a count of anything in that range that is another cell of dates. Can you please help me? Thanks.

    EX range list of dates

    then single cell of dates
    5/5/2019 5/12/2019 5/17/2019

  55. Kate Murphy says:

    With the "countif"function, can it count only whole integers? On my sheet, I want the abbreviation "RALR AM" to be counted as just a half, .5, so I can show I have someone just working half a day and not screw up my employee count for the whole day.

  56. Rebecca P. says:

    Hi, I can't seem to figure out the formula to do the following: I have 2 columns. Column A is either blank or a number from 1-9. Column B is blank, 1, or 0. I want calculate the number of 1s in Column B IF column A also equals 1. (I'll end up doing the same thing for 2-9).

  57. Tomas Gil says:

    Thanks for the great blog you've set up at Your enthusiasm is absolutely inspiring. Thanks again!

  58. Jay says:

    Hello There, I am trying to use a COUNTIFI have 2 different values in different columns resulting in 2 ranges.
    I want to count if the numbers from 1 range is less than or equal to the other range but I seem to be striking out. can anyone help?

  59. Rahul says:

    I want to calculate the particular product and their quantity sum
    Cell a(Sun). b(time). c(quantity).
    Product. X 0:05. 10
    Y. 0:10. 15
    X. 0:10. 05
    I have already applied one formula in this to calculate how much x and y is but now i want to add the quantity too
    The applied formula is. =Countif(a2:a34,"x",b2:b34,">="&e9,b2:b34,"<"&f9)
    In this fromula i have to add the c(cell) same quantity to add the sam in this formula

  60. Ane says:

    I have a column with dates and I am trying to count how many have a date in them. I have used =countif(c2:c2885,"*") and it works lovely on everything but dates. Any suggestions would be fantastic.

    • Derek says:

      Hi did you receive an answer for this question?

      I have a column with a long list of dates and another column with individual dates. I wanna count how many times the specific date in the second row comes up in the first row.

      EG: Countif(range,a1)
      Range: long list with multiple dates
      a1: cell with the specif date that we are looking for

  61. Toufik says:

    how can i create count formula where the first column is NOT blank AND the second column IS Blank.
    Please Its urgent
    Best Regards

  62. James says:

    I am trying to set a formula that if a total is less than a set amount then that amount is charged at 1 rate but if it is greater than x then times by another amount. i.e. if it is ,3.7 then *30 but if >3.7 *20
    can anyone help ?

  63. Mueen .Hasan says:

    Hi, I have five columns. I have created created a drop down menu list having three (empty column, 0 & 1). I want to use such formula that count all number of columns (even empty columns) whenever a value is entered in one or more columns. Explaining further, if a value is entered in first column and remaining columns are empty, the sum should be 5 (i.e 1 column that contain a value, and 4 remaining empty column). Help me Please!

    The main question is that it should count only when there is a value (0,1) in any of column and it must count all the column either there is a value in column or it is empty.

  64. Ivor Chan says:

    Hi there,
    I am trying to create a database and am struggling to count the number of clients between certain ages and certain admission dates. I am using defined names and my formula looks like this:

    However I am not getting any results when there should be at least 1, any advice or workarounds? I am unsure which part of the formula is incorrect...

    • Ivor Chan says:

      Sorry typo in formula above, refer to this:

  65. Lautaro says:

    Thanks for these enlighten tips.
    I just have a comment with respect to the unique/duplicate count.
    (from your example 3)
    Sometimes, we need to count unique elements in a row or column BUT that count should include one instance of the duplicated element as well (as you surely note, this a reasonable conceptualization of the unique definition). Is there a simple formula to include those single instances?


  66. VK says:

    My Formula
    =COUNTIFS('Daily Activity Register'!$M:$M,"Vidhu.Khosla",'Daily Activity Register'!$B:$B,"Walk-in",'Daily Activity Register'!$AH:$AH,"N",'Daily Activity Register'!C:C,J18) isfailing When last condition i am trying range of dates.
    Daily Activity Register'!C:C is dates

  67. Abdul Rehman says:

    Monthly Stipend Sheet of Research Scholars

    SrNo. Scholar Name Gender Stipend
    1 Mr. Muhammad Male 69,000
    2 Miss Farzana Female 45,000
    3 Miss Zanub Female 55,000
    4 Mr. Arslan Male 59,000
    5 Mr. Shafeeq Male 59,000
    6 Mrs. Faiza Female 62,000
    7 Mrs.Shagufta Female 55,000
    8 Mr. Ali Ahmad Male 68,000
    9 Mr. Kashif Male 45,000
    10 Miss Saima Female 69,000
    11 Mr. Salman Male 62,000
    12 Mr. Asim Male 55,000

    2. In cells “H8” and “H9”, enter a formula to calculate the total stipend for males and females.
    3. In cells “I8” and “I9” calculate the average stipends for males and females using the values calculated in question 2.
    4. In cells “J8” and “J9”, calculate the number of males and females having stipend more than 50,000.
    Please please help me with this.

  68. Shahram says:

    hi there
    i got a question , how can i make a formula that doesnt count 0 but show less then 0, for example in my file i got a row that show my active items in store and in other row it shows the number of negative margins from that item
    i did this formula for it
    but it count the "0" number for "AC", i just want lower then 0 not the zero number

  69. edzzza says:

    I need a function which one would count in range (A1:A999) words YES, but starting from last NO, for example YES YES NO YES YES YES, answer would be 3. Anyone could help please?
    Thank you in advance :)

  70. Andy says:

    I have following text values in cells:
    How to do countif to find only cells containing <7 expression?
    Any ideas?

  71. Dr Sandip Ratna says:

    Please help,
    In my Data, 1st column Date (reflecting 120 days)2nd column repeated names against various dates of 1st column (like A, B,C,D etc). how to count number of A,B,C,D between two dates? please help

  72. Santanu says:

    1st column salesman , 2nd product name, 3rd quantity of sale,,,salesman name & products r repeated,,,so it is possible that with countif formula salesman sales same product many times & how much sales? Results should be sum not count 5tine,,,result example - 1st time 10, 2nd time 14, 3rd time 7= result should be 31 not 3

  73. Michiel Feys says:

    I have the following problem. I want to be able to track the number of leads I got on my website during the year, or perhaps even over several years of time and plot this in a graph. I have a column with the date I got the lead and a column with the time I got it.

    I can use count if to get a column with the number of times a certain data appears. I would like to do this for every date in the year. For example 20 leads on January the first, 25 on January the second, etc. etc.

    How do I do this without having to input every single date of the year?

  74. Embassy Obih says:

    You have to sort out your days of the year against time. In column A sort the Dates and in column B sort the time.
    Column C
    Column D

    Column to get the above sorted date and time count


  75. Muhammad Atif Naseem says:

    Dear, Please solve my Problem. i try to develop formula of COUNT, but not count Duplicate value.

    Inv. No.

  76. Michelle says:

    Hi I wanna count how many awaiting report and good run column A with a criteria of today-2days. I'm having trouble building a formula please. Thank you

  77. Cliff says:

    Hello. I think I'm on the right track and the COUNTIF function is what I need.
    Say I have a table in column C with some blank cells, and I want to add an adjacent column D but omit the values in column D which are a next to a blank cell in column C. Would I use the COUNTIF or SUMIF function? the cells in column D need to be summed up. Also, can you help me out with the syntax?


    • Dan B says:

      Yes you can, but you'd want to use COUNTIFS as this allows for multiple sets of criteria.
      something along the lines of:
      if your using numbers then
      if your using text

  78. KYLE MINNETT says:

    Hello, I have a dynamic data set that is being changed daily (new rows being added to the top) and I want to have a unique ID created for each distinct set on "today's" date.
    so for example the data-set may look like the following
    nov 2, 2019 5704
    nov 2, 2019 5704
    nov 2, 2019 5705
    nov 2, 2019 5705
    nov 2, 2019 5706
    nov 1, 2019 3416
    nov 1, 2019 3416
    nov 1, 2019 5704
    oct 31, 2019 5705
    oct 31, 2019 5705

    So on the date of Nov 2, 2019 I have 3 distinct rows. I will be adding new rows to the top every day by using a macro so the data range should extend down in order to continue capturing all the data. My preferred range would be something like A:2 to A:3000

    as of right now I have a formula that tells me how many distinct sets I have in the entire data-set but I dont want that I want it to be able to look at the entire data set and only tell me how many distinct sets there are on today's date. I have tried a couple of things and I cant seem to figure it out. Here is the formula I currently have
    =SUM(IF("range of column 1""",1/COUNTIFS("range of column 1","range of column 1","range of column 2","range of column 2"), 0))

    Extra Note:
    If I were to apply this formula to the data set i have above it would give me a result of "6"

  79. EvieF says:

    Hi Team
    I have a spreadsheet which includes data on customer enquiries including customer ID, enquiry type, date, referral source etc. To count the enquiries by business area (made up of different referral sources) in a given month, I am using the following formula:
    =SUMPRODUCT(COUNTIFS('MYREPORT'!$H:$H,"ENQUIRY TYPE A",'MY REPORT'!$B:$B,CATEGORYLIST!$H$2:$H$27,Enquiry_Date,">="&P2,Enquiry_Date,"<"&EDATE(P2,1)))
    P2 in the above is the month I am totalling enquiries for. The reason for the 'CATEGORY LIST' part is so that I can group various different referral sources (people) into one reporting point e.g. 'TEAM A'.

    My problem:
    I need to be able to count a different enquiry type, 'ENQUIRY TYPE B' in the same way, but want to exclude any occurrences where the same client has already had an 'ENQUIRY TYPE A'. I am finding this difficult especially as my initial formula above does not refer to the unique client ID field and in order to exclude clients who already have had an 'ENQUIRY TYPE A', I am presuming it needs to.

    Many thanks for any help you can give

  80. Harry says:

    Thank you for being very helpful, I am looking for an answer to my question, Is there a formula to this situation?
    Name Amt
    AA 40
    DD 45
    AA 48
    SS 54
    AA 67
    GG 85
    AA 12
    AA 6
    LL 15
    QQ 4
    Count all # of "AA" if there is a value in column "Amount"

    • Hello Harry,
      If your task is to count all the AA entrances that have any amount, please use the formula below:

      If this is not exactly what you need and you'd like to sum these amounts, here is the formula for you:

  81. Boobile George says:

    I would like to know the answer:
    How to count the number of leaves in a year using a formula. Like how to capture those dates that I have taken as leave.

  82. John says:

    This is wrong:
    It should be:
    Please update.

  83. Jade says:

    I have 4 employees that maintain multiple properties monthly. I need a formula that will calculate how many properties are still needing completed per employee. I have the employees in one column and dates completed in a different column. I need it to count the blanks for each individual employee.

  84. Praveen Sharma says:

    A1 AHU
    A2 AHU
    A3 Boiler
    A4 Boiler
    A5 Boiler
    A6 Chiller
    A7 Chiller
    A8 Chiller
    A9 Chiller
    A10 Chiller

    How to count that How much AHU,Chiller & Boiler in my sheet. I Have 1000 Data in sheet.

    • Nikki says:

      Hi there! If you require to know how many of each "AHU", "Boiler" and "Chiller" are in your column, then I hope you find this useful.

      In cell A1, let's assume there is a "Product Type" header. In cell B2, create a "AHU" header. In cell C2, create a "Boiler" header. In cell D2, create a "Chiller" header. The headers in cells B2, C2 and D2 will be your target cell references.

      Let's assume "AHU" is in cell A2, "AHU" is in cell A3, "Boiler" is in cell A4, etc.

      To count the total number of "AHU" in column A, the formula is: =COUNTIF(A:A,$B$1) the result should be 2. To count the total number of "Boiler" in column A, the formula is: =COUNTIF(A:A,$C$1) - the result should be 3. To count the total number of "Chiller" in column A, the formula is: =COUNTIF(A:A,$D$1) - the result should be 5.

  85. Gavin Hewison says:

    I have a table with a range of cells in one column that I want to count from one cell.
    i.e Column P cells 1 through 10, each cell represents a repair number "P1 = Repair 1, P2 = Repair 2 etc etc.
    In this range I want all cells to count if the repair numbers are in one other cell ie if cell Q1 has 1, 2, 3, 4, 5, 6 typed in it I would like cell P1, P2, P3, P4, P5, P6 to each show 1. Then if cell Q2 has the same 1 through 6 typed same as Q1 I want cells P1 through P6 to each show 2.
    Hope you can help

  86. Cameron says:

    What if I'm counting less than / greater than / equal to between times? I have an expected arrival time (column D) and an actual arrival time (column I) listed in HH:MM:SS time and I need to know how many groups arrived before after or exactly when they expected.

    Bonus points if I can add another criteria to account for the type of visit ( SGV, Program*, Group)

  87. Fathima says:

    I would like to count values based on dates with 2 criteria's.
    I need the count of unique tickets worked by agent on single day
    (If same ticket is repeated multiple times on single day it should count as one but if same tickets is worked on different date then it should increment by 1.
    Please help.
    The formula used here is counting unique tickets for whole month
    Formula used: 2 attempts made but same result
    1st attempt : =SUM(IF((H6=$H$6:$H$27)*($E$6:$E$27<=DATE(2020, 1, 31)), 1/COUNTIFS($H$6:$H$27, H6, $F$6:$F$27, $F$6:$F$27, $E$6:$E$27, "<="&DATE(2020, 1, 31)), ""))
    2nd attempt: =SUM(--(FREQUENCY(IF(H6=$H$6:$H$25, COUNTIF($F$6:$F$25, "<"&$F$6:$F$25), ""), (COUNTIF($F$6:$F$25, "0))
    (wrong value)
    Date TICKETS DISPATCHED ASSIGNEE Total ticket Correct value(to be)
    2020-01-24 INC123 Pretty Deena Mathew 2 3
    2020-01-24 INC123 Pretty Deena Mathew 2 3
    2020-01-25 INC123 Pretty Deena Mathew 2 3
    2020-01-24 inc1234 Pretty Deena Mathew 2 3
    2020-01-25 ritm012 Rachit Sharma 1 1
    Here, first 2 rows value should count 2 as same ticket is worked on single day by one agent,
    3rd row should be counting 3 as same ticket is worked on NEXT DAY..

    This is very important to resolve.
    Please assist
    Thanks in advance

  88. rashid says:

    c column is differnt date and the f column is number..
    i want to search how many blanks cell of column c date..
    c column f column]
    07-02-2020 23565
    07-02-2020 2454
    08-02-2020 251454

    07 february 01 cell empty and 08 february 02 cell

  89. Kirsty says:

    Hello, I really hope someone can help me,m this has come up a few times at work and we really cannot figure it out!! we want a formula for 2 columns, so.. if column A says 'apple' to count it, but only count it if column B is greater than 60.

    I hope someone can help me!!

  90. Tom says:

    The `COUNTIF(range,""&"")` will fail in situation like this: A1 = "abcd", A2 = "1234", A3 = IF(COUNTA(A1)>0, "", A1). The result will be 3 which is wrong because A3 is technically a blank cell.

  91. Ashley says:

    I am trying to count the number of shifts a person works between a date range, but the shifts are on a 4 on, 4 off schedule.
    In a separate tab, I have the start & end dates of each of the shifts till the end of the year. How do I tell it to count the number of days that someone would work, on this shift, given their start & end date of employment.

  92. Carl says:

    This is FABULOUS! Thanks!

  93. Katu says:

    Hi there.

    I have data with students performance in a particular classroom.
    Data Columns: Names, Age, Gender, class/grade, Attendence, Performance status, performance status update.

    Options of performance status / update are: poor, good, excellent.

    I want to run some queries on this data in a summary table giving the following information:
    1. Count Attendance by: Age, Gender and Class/Grade
    2. Count Performance by: Age, Gender, Class/Grade whereby I count Performance Update when both Performance Status and Update have values and count performance status when Update is blank.

    Please help. I was thinking of using class/grade as a filter for the summary table such that I can switch between classes easily as well as having overall school summary

  94. Ecko says:

    Hi i just seek your assistance, i am trying to count the number of request i've send to a specific person in a day and also the number of answer i receive with them i day also. Here's my problem my formula doesn't count right. Here's my formula in Sheet1 (=COUNTIFS(March!O:O,">=" & Summary!B5,March!O:O,"<=" & Summary!B5+1)) details are from Sheet2 with a date and time format for March. Summary!B5 means the day today or next day. Hope you can assist me thanks.

  95. Madeline says:

    I am trying to count the instances where a particular string of text is found in column A, and the date in column D is within a specific range. Could anyone tell me why this isn't working?
    =COUNTIFS(Datasheet!A:A,"*theme*",Datasheet!D:D,">=6/1/2019", Datasheet!D:D,"<=6/30/2019")
    Thank you!

  96. Adina says:

    Hello, I am trying to count the number of cells within a range of cells that fall between two values. So my values which range between 1000 and 50000 are located in cells I2 through I21. I put a value of 5000 in N4 and 25000 in O4. I want to count how many times the values in cells I2 through I21 fall between 5000 and 25000.

    When I use this formula: =COUNTIF(I2:I21,">="&N4)+COUNTIF(I2:I21,"="&N4)-COUNTIF(I2:I21,"<="&O4) I get 3. This is also incorrect because I can count the cells and see that the answer should be 7. 7 out of 20 cells fall between 5000 and 25000.

    Thank you.

  97. Adina says:

    Sorry, not sure what happened to what I typed above. Edited:
    Hello, I am trying to count the number of cells within a range of cells that fall between two values. So my values which range between 1000 and 50000 are located in cells I2 through I21. I put a value of 5000 in N4 and 25000 in O4. I want to count how many times the values in cells I2 through I21 fall between 5000 and 25000.

    When I use this formula: =COUNTIF(I2:I21,">="&N4)+COUNTIF(I2:I21,"="&N4) I get 27.

    When I use =COUNTIF(I2:I21,">="&N4)+COUNTIF(I2:I21,"="&N4) I get 3.

    I can count the cells and see that the answer should be 7. 7 out of 20 cells fall between 5000 and 25000.

    Thank you.

  98. E says:

    Hi, I want to count those items which have 3 letters + 4 numbers from below, how to count it please?


    • Hello,
      Please try the following formula:

      =SUMPRODUCT(--NOT(ISNUMBER(--LEFT(C1:C14,1))), --NOT(ISNUMBER(--LEFT(C1:C14,2))), --NOT(ISNUMBER(--LEFT(C1:C14,1))), --ISNUMBER(--RIGHT(C1:C14,1)), --ISNUMBER(--RIGHT(C1:C14,2)), --ISNUMBER(--RIGHT(C1:C14,3)), --ISNUMBER(--RIGHT(C1:C14,4)))

      I hope it’ll be helpful.

  99. Tilly says:

    Hi there! I am trying to combine these formulas so that both criteria must be true for it to be counted however as my ranges are different sizes this is problematic with the countif function. Is there a way around this?
    These are the two formulas which need to be combined:

    =COUNTIF('Volunteer Call Handler Form APRIL'!Q:V,"XXXXXXX - recent")
    COUNTIF('Volunteer Call Handler Form APRIL'!G:G,"Inbound")

  100. Naledzi M says:

    Hi i have a spreadsheet Songs and i need to use Simplex criteria to get the names of the row, which formula do i use?

Post a comment

Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)