Jul
2

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 2013, 2010, 2007 and 2003.

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.

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:

=COUNTIF(range,"*")

But the fact is, the above formula counts all cells that contain any text, meaning that cells with dates and numbers will be treated as blank cells!

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

=COUNTIF(range,"<>"&"")

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

COUNTIF blank

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.

=COUNTIF(B2:B9,">5")-COUNTIF(B2:B9,">=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:

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

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:

=SUMPRODUCT((COUNTIF(B2:B1000,C2:C1000)>0)*(C2:C1000<>""))

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:

=SUMPRODUCT((COUNTIF(B2:B1000,C2:C1000)=0)*(C2:C1000<>""))
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:

=SUMPRODUCT((COUNTIF(A2:I2,A2:I2)>1)*(A2:I2<>""))

Count unique values in a row:

=SUMPRODUCT((COUNTIF(A2:I2,A2:I2)=1)*(A2:I2<>""))
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:

=SUM(COUNTIF(INDIRECT({"B2:B8","D2:C8"}),"=0"))

=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!

Want to find more inventive tips, tricks and tutorials?

Please follow us on Google+:

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

  1. Karo says:

    I want to group or count values that start the same. In this short example YTOP.125 should be grouped together - or counted. I can work with either. So I want to be able to say "if the first, for example 8 characters are the same then...
    For example
    ColumnA
    YTOP.125SP
    HYEP.556M
    YTOP.648
    MNTK.593
    HYEP.257
    YTOP.125LO

    Can you help? Is this possible?

    • Hi Karo,

      Yes, it is possible, but you will need to add a helper column to your table. If this is acceptable, then create an additional column and copy the following formula across that column, which will extract the first 8 characters =LEFT(A2,8). After that, you can proceed in 2 ways.

      Way 1 – using Subtotal:
      - Sort your table by Column A or the newly created column with the above formula.
      - Apply subtotal to the table (Data >Outline >Subtotal) with these settings:
      At each change in : Column with the formula
      Use function: Count
      Add subtotal to: Column with the formula
      - Click Ok.
      - Then Press Number 2 at the left side of you sheet and you will get the count of codes by the first 8 characters.

      For more info about using subtotals please see this article - Using Subtotals in Microsoft Excel

      Way 2 – using a pivot table:
      - Insert a pivot table (select your table and go to Insert > Tables > PivotTable).
      - Place your pivot table onto a new sheet.
      - Drag and drop the column with the formula to the Rows section and column A to the Values section.

  2. Richard says:

    Hello,

    I want to formulate the following:

    IF "cell value A" greater than(>) "cell value B", THEN "cell value A" = "cell value B" and therefore the result "cell value C" is equal to zero

    if this is doable can it be applied to multiple "cells"?

    • Hi Richard,

      I understand your condition but cannot figure out what result you want to achieve : ) If you want a formula to add a zero to column C if a value in cell A in the same row is greater than in cell B, you can use the following IF formula: =IF($A2>$B2,0,"") where 2 is your first row with data. Since we use a relative row reference (without the $ sign) the formula with compare values in each individual row when copied across multiple cell. If you are looking for some other result, please clarify.

  3. Kelly-Ann says:

    Hi There,

    I am trying to record attendance at my office and I am putting together an excel spreadsheet to store the data. I need to be able to sum up the totals of vacation days, personal days, business days, and sick days. The countif works well if I put 'V' for vacation, 'B' for business and so forth, but how can I calculate half days? Is there a way that I can set up a second Countif function so when I put in something like 'VH' in the range it counts it as 0.5 rather than one? Pretty much I am wondering if I can change the amount that the countif function counts by.

    Thanks

  4. Clare says:

    Hi,

    I have a project list where activity is shown in a timeline. I need to count up if a column 'project stage' shows activity in any given month. I have set the countif for picking up activity but now need to make it so that it only counts that activity if column 'project stage' is a particular word.

    Any idea?

    BTW the above is great!

  5. Ruth says:

    Hi,

    I need to combine an IF statement with a COUNTIF that will count 3 out of 5 text entry options for a single column. If the COUNTIF =0, it needs to show as a dash. This is the original formula that is working =IF(COUNTIF(B1:B10000,C1&D1)=0,"-",COUNTIF(B1:B10000,C1&D1))

    I need to add another criteria to the COUNTIFs, where there are 5 possible entries in column E and I need it to count 3 of these (Allocated, Declined, Rejected). Any suggestions is gratefully appreciated.

    Ruth

    • Hello Ruth,

      Let me check if I understand the task correctly. Do you want to count values in column B only if column E has any of these values: Allocated, Declined, Rejected? Or, are you looking for something different? It will be very helpful if you can paste the values from a couple of rows in columns B-E and the result you expect a formula to return.

  6. Dean BROWN says:

    Hi There,

    I am wanting to extract some values and I thought COUNTIF might be able to do it.
    I have a cell that contains some data eg: ABC-BBC-ABC-SII-BBC-ABC
    I get COUNTIF to count how many times BBC appears in that cell, seems it only counts the FIRST instance of BBC.
    How do I get it check the entire contents of the cell and correctly record the result eg: BBC appears twice in that cell

    Thanks!

    • The COUNTIF function cannot count specific text or characters inside a cell, it deals with the entire contents of a cell, as if you selected the option "Match entire cell contents" when performing search in Excel.

      Please use this formula instead:
      =(LEN(A2)-LEN(SUBSTITUTE(A2,"BBC","")))/LEN("BBC")

      Where A2 is the cell containing "ABC-BBC-ABC-SII-BBC-ABC", and "BBC" is the search text. Instead of "BBC", you can enter a cell reference with the search text, e.g. B1, sometimes it is a more convenient way to work with formulas.

  7. Robert says:

    I'm after something similar to what you were asking Ruth please.
    I've set up the formula to count up all the instances of "*banananas*" in column B.
    I then have a list of places in column A. So say the total amount of banananas is 300 I want to know how many banananas I have for each of the individual places. So it only counts the banananas entries that are next to specific text in column A.
    Thanks.

  8. lara says:

    Hi

    Im also after something similar to Robert.

    In column A i have a number of different status ie. "On Programme", "Rejected", "Completed" etc. and in column B i have a number of programmes ie. "Cleaning", "Customer Service", etc and i want to could how many for example On programme cleaning there are?

    Ive tried and other formulas similar to this but none seem to be counting what i want.
    =SUM((COUNTIF(A2:A32,"On Programme"))*OR((COUNTIF(B2:B32,"CLEANING"))))

    Can you help?

  9. V Sorensen says:

    HELP! I'm trying to create a list of email addresses, based on wireless carrier. So if one column has their wireless number, next column has carrier, I then want to combine wireless number with the appropriate text email. Where these are the extensions I want to add:

    Verizon = @vtext.com
    AT&T = number@txt.att.net
    Sprint = number@pm.sprint.com
    Tmobile = number@tmomail.net

    So if someone lists
    5556667777 Verizon
    6667778888 Sprint
    How do I create a cell that says If A6=Verizon, then 5556667777@vtext.com??
    It's a list of a few hundred, so I'd rather no do manually. Thanks so much for the assistance!

    • Hello Virginia,

      To fulfill your task, you need to create a lookup table "CarrierName = Domain" in another worksheet. Then, you will be able to pull a domain by the Carrier name using the below formula below and concatenate it with the telephone number. Here is the formula:
      =IF(AND(A2<>"", B2<>"", MATCH(B2,WirelessCarriersData[Name], 0)>0), A2 & VLOOKUP(B2,WirelessCarriersData,2,FALSE),"")

      Where A2 is the first cell in the Phone column not including headers, B2 is the first cell in the Carrier column, and WirelessCarriersData is the name of the lookup table.

      We have created an example for you, feel free to download it using the link below:
      http://www.ablebits.com/_img-blog/_comments/virginia.sorensen@capitalone.com-1.xlsx

  10. Dawn Weber says:

    I am trying to create an auto Vacation equation template. I have completed the template and it is working well except, I would like it to do something like this.

    Example:

    =COUNTIF(D3:J20, "BBB-V") Currently the template is counting one for each one it sees, but what I would like it to is to equate 1 with a (NUMBER) like "10" to allow for actual hours employee is being deducted from balance the employee has. So if it sees one I would like it to see it is 1=10 and display the number in the cell as 10... Have I made this clear?

    • Hello Dawn,

      I am not sure I fully understand your task. You can try one of the following solutions:

      Solution 1 – you can just multiply the result by a certain number, say 10, othe formula will be as follows:
      =COUNTIF(D3:J20, "BBB-V")*10

      If you want to pull the number from a certain cell, replace "10" with a cell reference.

      Solution 2 – if you want to show 10 for COUNTIF=1, and for the other cases you want to do something different (e.g. multiply by 9):
      =IF(COUNTIF(D3:J20, "BBB-V") = 1, 10, COUNTIF(D3:J20, "BBB-V") * 9 )

      If neither solution work for you, please send a sample workbook with your data at support@ablebits.com and we will try to help.

  11. Rod says:

    Hi Svetlana!, I hope you can help me...

    I am making a sample-test exam where the correct answer will be hidden in column C and column B will be where the person who is taking the exam will place his/her answer. I need a formula to tell me how many correct answers were given. For example:

    -X1- = test takers' answer
    -X2- = correct answer

    -X1- -X2-
    A B
    C C
    A D
    D D
    B B

    I need a formula that will tell me that the test taker answered 3 questions correctly and then divide that value by the number of questions, in this case 5 questions. FYI: The answers will be side-by-side, however many blank rows will separate each answer (to facilitate the question and multiple answers in column A).

    Thank you,
    Rod

    • Hi Rod!

      You can use the following array formula. Change 30 to the number of your last row with answers, and remember to press Ctrl + Shift + Enter to complete the formula:
      =SUM(NOT(ISBLANK(C2:C30))*(B2:B30=C2:C30))

      Then you can divide the result by the number of questions.

      BTW, please be aware that one can easily see the content of a hidden column. More details are in this blog post:
      http://www.ablebits.com/office-addins-blog/2014/07/30/excel-unhide-columns/#disable-unhide-columns

      • Uldis says:

        Hi Svetlana!

        I tried to use this sample You provided for Rod, but it doesn't seem to work, and I don't get what's wrong...

        1) I entered some values in B and C columns
        2) entered formula exactly as it is in Your sample and pressed Ctrl+Shift+Enter
        as a result I see that formula as a text, and no result... why is that so? What am I doing wrong?

        Here is the screenshot how it looks:
        http://screencast.com/t/QRsaafuSvzw

        Hope, You can help!

        P.S. This is a really nice and helpful blog post!

      • Uldis says:

        OK, I gound what's wrong... i didn't know that brackets {} are added automatically and I don't have to enter them manually :)

        • Hi Uldis,

          It's great that you sorted this out! Those brackets might be confusing indeed, I added them to show that is an array formula. But may be I'd better remove them not to confuse someone else :)

  12. Samantha says:

    I tried using the countif(range,""&"") to count the cells within a range in my sheet that have something written in them and it keeps counting all of the cells selected.

    If I tried counting all the cells that are blank instead and write countif(range,""&"*") it brings me to 0.

    Any idea of why it wont count my cells that have data?

  13. Lance says:

    Trying to calculate the number of times certain words appear in a column. Some of the cells works others don't and I am using the exact same formulas for both. Even when I click in on the fx button the total is correct in the formula, but it won't replicate it to the cell. The cell just shows the formula an not the number. I have all the cells formatted to general, even the ones that are working correctly.

  14. Bilal says:

    Is there any site where we can get online support for MS office (Excel) I mean through chat...

  15. Gayle says:

    I need to count the number of names in a column, but exclude any that have been struck through.

  16. Shams says:

    Greetings
    Making an attendance sheet
    Want 5 T=Tardies to couny as a 1
    Ex. 28 periods a week one has 12 Tardies which means 2 absents so 112 periods a month in average so how or which formula can help me count 5 T=Tardies as 1

    Thanks in advance

  17. Sam says:

    Not sure if its possible, but can you Count the Blank spaces in One Column (say Column A) if there is specific text in Column B that corresponds to the blank area. For example, I need to know how many have responded to my invitation (blanks)in column A, but have informed me they are available (Word: Available)in column B. I have tried to combine a =CountBlank with a =COUNTIF but it doesn't seem to be working for me.
    Thank you!

  18. Alex says:

    Hi

    I want to be able:
    If column A = certain text, then I want all the Values in Column B(same row) be summed together:

    For example:

    Profession Mark
    Nurse 5
    Doctor 2
    Careworker 2
    Doctor 6
    Doctor 7
    Nurse 8
    Nurse 5

    Nurse Doctor Careworker
    (sum) (sum) (sum)

  19. Alex says:

    Hi
    Never mind, I figured it out.
    thank you

  20. Tommy says:

    Hello,

    I want to count the number of blank cells from a specific character.
    The problem is that the range is extended with one cell, each day. Can you automatically extend that range each day?
    Also, eventially the specific character will appear again in later cells, then I want it to start the count of blank cell from this "new" specific character.

    Example:
    The letter P is in cell B2 the range is B2:G2, hence 5 blank cells. Two days after I want the range to be B2:I2 i.e. 7 blank cells.
    Another three days later the the letter P appears in cell K2 (range is now B2:L2), hence it should start the count from K2 i.e. 1 blank cell.

    Thank you
    Hope I didn't complicate the explaination?! =)

    Thank you

  21. Heather says:

    I am trying to identify and account for duplicates. I would like the COUNTIF formula to tell me if there is a duplicate anywhere else in that column. If a unique value it would result in a "1" and if a duplicate is found it will result in a "0." However, I would also like the 1st occurance of a value to result in a "1" and all other duplicate values to be a "0." How can I get that 1st occurance of a value to result in a "1" separate from its duplicates?

  22. Pina L C says:

    This is so useful Svetlana, and your answers to the questions here helped a lot today at work. Thanks!

  23. Carol says:

    Hi. i have 6 sheet tabs. The 5 sheet tabs are the summary report of 5 different branches and the other sheet tab is for my summary report. All column A of 5 sheet tabs refers to different sports while column B refers to number of votes for different sports. I need to get the top 3 sports for all branches.

    For example,
    In Branch 1, the top 3 sports listed are basketball, football and volleyball with 85, 74 and 65 votes. For branch 2, the top 3 listed are tennis, basketball and badminton with 65, 54 and 32 votes and so on. How will i get the top most sports from 5 different branches that will also add the votes if for example all branches have basketball in their list? Hope you can help me with this.

    Thank you

  24. Al Imran Monju says:

    Hi,
    I have seen this. When i feel that i have need more than i will find out. But for this time it is very helpful to me. Thank you.

  25. Al+Imran+Monju says:

    Hi,
    I want to know from you that how can I count the two cell data at a time like:

    ColA Col B
    1 M
    2 F
    3 F
    6 M
    2 F
    3 M

    I want to know for 1 how many M of F and for 2 how many M or F Like ------------

  26. Karla says:

    Can you please tell me what the formula is if I want when if a # is less than 35 then add $5.75 if it's greater than 35 add $12.75 please help thanks

    • Hi Karla,

      I am not sure I can exactly follow you. Do you want to enter a number in some cell based on a value in another cell? For example, if a number in cell A1 is less than 35, than put 5.75 in cell B2, if it's greater than 35, than put 12.75? And what if it is equal to 35?

  27. Rick says:

    Does the COUNTIF function only compare the first 15 characters? I use it to check for duplicate values. It flags 2 of my cells as being duplicates. The only difference is the 16th character.

  28. Johann Sebastian says:

    I have a spreadsheet where I input my current weekly sales in Column C and last year's weekly sales is already typed in Column B; I want to make a REAL month-to-date weekly comparison at my weekly manager's meeting.

    To do this I would need the weekly sales from last year in Column B to count ONLY when if I input the weekly sales figure in Column C; otherwise I need Column B "blank" or to count as "0".

    Note: please keep in mind that last years weekly sales in Column B is taken from last year's monthly sales in Column A divided by 4 (accounting for four weeks in a month) whereby the value of cell B1 is "=A1/4"

    So, I need Column B to display last year's weekly sales ONLY when I input last week's sales in Column C.

    Can someone please help me?

    Thank You,
    Johann Sebastian

  29. Dushyant Dodiya says:

    I have one problem in my work sheet.

    Pls any one help me.

    I have total no. of data.
    Like

    Year Total 1 2 3 4 5
    of Cell

    1993 5 136 591 888 960 1006
    1994 3 107 177 347
    1995 5 408 1238 1352 1521 1581
    3 2215 2414 2444
    1996 5 112 1093 1144 1298 1351
    1997 5 122 238 388 458 485
    4 1104 1105 1432 1479
    1998 5 77 88 464 467 574
    5 1165 1182 1190 1374 1430
    1999 5 421 480 531 844 900
    4 1913 1935 2005 2019
    2000 5 16 36 79 95 97
    5 572 574 655 656 674
    5 1209 1275 1280 1481 1507
    3 1834 1851 1875

    i want the when ever year combined that time total of cell sum.

  30. Vamsi says:

    HELLO,
    Please help me in this problem

    Formula: =IFERROR(LOOKUP(C2,Sheet1!$A$30:$A$39,Sheet1!$B$30:$B$39), "")

    Required condition: If C2 cell in not equal to A2 " additional conditon to be added in above formula with the existing.

  31. Vamsi says:

    Hello,
    DESCRIPTION OF FORMULA MADE IN EXCEL WORKSHEET.

    C2 WILL BE ENTERED BY USER.
    In D2 the entered value of user will be searched (LOOKUP)in other worksheet with in specified range of column (Sheet1!$A$30:$A$39) and output will be the specified range in other column (Sheet1!$B$30:$B$39).
    If there no entry in the cell (C2) by user the cell (D2) will remain Blank.

    Here my requirement:
    In addition to above condition the value entered in C2 should not be equal to Value of A2, if equal then it should be an error message (N/A).

    PARAMETERS OF THE CELLS in worksheet 2:
    A2 DUTY CODE NORMAL
    C2 DUTY CODE OVERTIME
    D2 TIMING OF DUTY

    Sheet1!$A$30:$A$39 - Worksheet 1 A30 to A39 are duty codes
    Sheet1!$B$30:$B$39 - Worksheet 1 Duty timing

    ---END---

  32. Rachana says:

    Hi Svetlana,

    What formula can i use if i want a value "N" in 1st coulmn based on blank cells that appear in 2nd column and want value "Y" in 1st column based on non-blank cells that appear in 2nd column. Please advise.

    Y/N Names
    N
    Y abc

    Thanks

    • Hello Rachana,

      You can use the following formula for your 1st column:
      =if(B2<>"", "Y","N)

      Where B is your second column.

      • Rachana says:

        Thank you Swetlana!!!

        One more help i would need on a formula.

        I have one column "A" with names e.g. Mike, Tony etc goes till A12, and the names are repeated.
        other column "B" having some numbers with % sign e.g. 90%, 80% etc.. goes till B12

        Now i want to count non blanks ones in B1:B12 for Mike in B1:B12

        I tried as below , but it didnt work. Please can you advise which forula fits better..

        =countifs('Block Inspections'!B1:B12,'Block Inspections'!A12:A150,"=Mike")

        Thanks,
        Rachana

  33. Amber says:

    Hi Svetlana,

    I have three columns that are populated via a pivot table (in tabular format). They are site (a), manager (b) and rep (c). Only one cell populates per row across the three columns. I'm trying to build 1 formula that can be applied to all rows in one column (d), that counts the # of items entered in a given month for either site, manager or rep...depending on which column (a-c) is populated in that row.

    I recently built the below formula that looks at the rep column first. If it is populated, it counts the # of times that reps name shows up in column b on the data tab. If it is blank, then it knows it needs to refer to column b for the manager name and then count the # times that managers name shows up on the data tab in column f.

    =IF(C6="",COUNTIF(DATA!$F$3:$F$3000,B6),COUNTIF(DATA!$B$3:$B$3000,C6))

    I need help adding an additional (nested if or and) function that says if both column b and c are blank, refer to column a and then search the # of times that site shows up in column d of the data tab. Let me know if I am not explaining this well enough. I appreciate your help in advance!

    Thanks!

  34. Vamsi says:

    Hellow Svetlana Cheusheva, Forwarded the workbook file by mail for your guidance and support. Hope you can help me.
    Thanks

  35. COUTNING says:

    I need a formula that says:

    if column I = cell A1 count it, but if column I is blank then go to column g only count it if column g = cell A1

  36. Satheesh says:

    If a sheet arranged as bellow
    Col A col B col C
    Name rank age
    Arun dy co 25
    Philips si 30
    Joseph dy co 27
    Vinod dy co 29
    stphen si 26
    Then how can count how many dy co become between the age 25 to 30 by using excel formula.
    Please help

  37. Rachana says:

    One more help i would need on a formula.

    I have one column "A" with names e.g. Mike, Tony etc goes till A12, and the names are repeated.
    other column "B" having some numbers with % sign e.g. 90%, 80% etc.. goes till B12

    Now i want to count non blanks ones in B1:B12 for Mike in B1:B12

    I tried as below , but it didnt work. Please can you advise which formula fits better..

    =countifs('Block Inspections'!B1:B12,'Block Inspections'!A12:A150,"=Mike")

    Thanks,
    Rachana

  38. Rachana says:

    Hi Swetlana,

    Just to add on above query, i am counting them on another master sheet.

    Thanks,
    Rachana R

  39. Rachana says:

    Hey Swetlana,

    I just tried as below as it worked :)

    =COUNTIFS('Block Inspections'!$A$12:$A$150,"Mike",'Block Inspections'!$D$12:$D$150,"")

    Thanks,
    Rachana

  40. Rachana says:

    Oops i tried as below with the non blank ones and it worked. :)

    =COUNTIFS('Block Inspections'!$A$12:$A$150,"Mike",'Block Inspections'!$D$12:$D$150,"")

  41. Jeremy says:

    Hi i am trying to come up with a formula that I can find the duplicate order ID in one column and have it subtract the dates in another column so I can figure out how long it took to ship.

  42. Rachana says:

    Hey Swetlana,

    Please can you advise on below 2 quesries.

    1) I have A column that shows a start date e.g. 10-1-2014 and the task needs to be completed by 6 weeks. So i want the B column to auto calculate the date of completion that is 6 weeks from the start date. Please can you advise how can formulate this one. And like also want to calculate for other target dates i.e. after 26 weeks in another column and 52 weeks in another column.

    Col A(start date) Col B(6 weeks target date)
    10-1-2014 ? (How to auto populate date)

    1) Now I have two columns as below, please can you advise.
    Column B shows - target date to be completed and the column C is showing actual date for the task completed. Now i have column D where i want value Y or N based on date of completion is greater or lesser than the target date. So if completion date is greater than target date than it should show N on Column D and similarly Y where the date is equal or lesser than the target date. Please can you advise how can set a formula for this one.

    Col A Col B Col C Col D
    start date 6 weeks target date Actual date Y or N ?(auto populate)
    01-09-2014 ? Auto populate 20/10/2014 N - Value to auto populate

    Thanks,
    Rachana

    • Hello Rachana,

      Here's the formula for column B (cell B2): =A2 + 6*7
      And this one is for column D: =IF(C2<=B2, "Y", "N")

      • Rachana says:

        Thank you Svetlana :) It worked

        Now when i use =IF(C2<=B2, "Y", "N") and along with that i want to add one more criteria that if the completion date column is blank then the Y/N column should remain blank. Please can you advise.

        Thanks,
        Rachana

        • Rachana,

          Assuming that your "completion date column" is column C, here is the formula:
          =IF(C2="","",IF(C2<=B2,"Y","N"))

          • Rachana says:

            Hey Svetlana,

            Lets say an A column has Start date that is blank and i want to add a formula on C stating that if A1 is blank then C1 should be blank but A1 has a start date then it should calculate A1+6*7 for the date to auto populate on the 6 weeks date. Please can you advise.

            From your previous reply i see we can use =IF(A1="","") for having blank cell but not sure how to further add the criteria as above. Please advise me.

            Thanks,
            Rachana

          • Rachana says:

            Thank you Svetlana for formula =IF(C2="","",IF(C2<=B2,"Y","N"))
            It worked as desired. :)

            I would need your help on below to get a formula.

            I have a A column with start dates in the format e.g. A1= "01/01/2014", A2= 1/3/2014, A3= 1/2/2014. Now i want column B to auto enter the value in B1 = January, B2= March, B3= February..

            Just to highligh that the Start date is not in order hence the months are not in order too. please advise which formula can be applied to have respective months value in B column based on Start date in A column.

            Thanks,
            Rachana

            • Rachana,

              You can simply enter the formula =A1 in cell B1, and then copy it down to other cells of column B. This will populate column B with dates corresponding to column A. Then select column B, right click and click Format Cells > Number tab > Custom, and type mmmm in the "Type" box. mmmm is the date format that displays months only.

  43. Syed Bukhari says:

    Hello Svetlana,

    I'm looking to count the number of occurrences of text in column A while checking the value in column B to be >0.

    Let's say if my Text repeated for 5 days a week having value higher than 0. I need 5 in front of that text.

    Appreciate your help!
    Syed

  44. Ashish says:

    Hi, Is there any formula to capture data from other cell if a particular cell is blank, for example A1 and B1 are the cell,if A1 is blank then data should be capture from B1.

    Regards
    Ashish

  45. Ashish says:

    thanks Svetlana,it worked and saved my almost 10 mins from daily my work :).

  46. handri says:

    please help me Svetlana. I have a problem about sequence of numbers that is a lot to be able to read just one in a column. eg: column A there are numbers
    9,1,2,3,4,2,2,5,6,2,7,5,4,8,9,2,10, then I would like to read into 1,2,3,4,5,6,7 , 8,9,10. how the formula? thank you very much

  47. Zack says:

    I am trying to use the count unique values function as part of a COUNTIFS equation, I need to count the amount of 'P's in one column, that have a unique number in another column. I can't get the unique equation to work though. Please help.
    =COUNTIFS(E$7:R$42,">0",F$7:S$42,"P",E$7:R$42,"=1") Where did I go wrong?

    • Hello Zack,

      You need a helper column that will show whether the number in another column is unique or not.
      The formula can be as follows:
      =if(countif(D:D,D2)=1,1,0)

      Then you can use a value from this helper column as one of the conditions for your COUNTIFS.

      As I can see in your formula, several columns are used for each range. Is this correct?

  48. Khalid says:

    Hi Svetlana,

    I have data in Col that A.
    I want to know no of lines between each pipe (|)

    Expected Result in B col for
    first pipe is 0
    Second Pipe is 6
    Third Pipe is 7

    Data
    |
    1
    ZGF1213420
    S ILIYAAZ
    S AZEES MIAH
    34-53-22
    29
    |
    2
    ZGF0316174
    U SUMALATA
    NARASHIMHA
    MURTHI
    53/1
    21
    |

    Thanks in Advance,
    Khalid

  49. Rachana says:

    Hi Svetlana,

    Lets say an A column has Start date that is blank and i want to add a formula on C stating that if A1 is blank then C1 should be blank but A1 has a start date then it should calculate A1+6*7 for the date to auto populate on the 6 weeks date. Please can you advise.

    From your previous reply i see we can use =IF(A1="","") for having blank cell but not sure how to further add the criteria as above. Please advise me.

    Thanks in advance
    Rachana

  50. Rachana says:

    Hi Svetlana,

    I am using formula =IF(K5>=$A$1, "True", "False") where A1 is 01/04/2014. Now I want to add additional criteria to it as below, please can you advise.

    I have A1= 01/01/2014 and B1=31/03/2014
    I want C1 to calculate if D1 cell has the date that is >= A1 and <=B1 then put value True otherwise false.

    Thanks in advance
    Rachana

    • Rachana,

      You don't need the COUNTIF function for this task. Just add one more IF statement to your formula:

      =IF(D1>=$A$1, IF(D1<=$B$1, "True", "False"), "False")

      • Anonymous says:

        Thank you again Svetlana!!!

        Please help on another query.

        I am using a formula - =COUNTIFS('6-26-52 Weeks'!$F$4:$F$52,"=Mike",'6-26-52 Weeks'!$I$4:$I$52,"=April",'6-26-52 Weeks'!$J$4:$J$52,"=True",'6-26-52 Weeks'!$Q$4:$Q$52,"=Y")

        I needed the total count of Ys avilable in sheet 1 provided it matches the criteria that is - Col F to find the name e.g. Mike, Col I to check the month e.g. April, Col J to count True and then Count Y from Col Q.

        For some reason it doesnt give me any formula error neither it counts Y in the mastter sheet.

        Regards,
        Rachana

      • Rachana says:

        Thank you Svetlana!

        Thats quite helpful:)

  51. Farhana says:

    Hi
    I have Excel File with more than 1000 columns and rows and in some rows/columns, there are email addresses. I want to collect all emails only in one column. Can you please guide

  52. Lydia Noble says:

    I have sheet 1 "South", contains a range of criteria.
    In Column A lists codes containing N or M followed by a number ie N2, M4 etc. In Column C lists New Birth, 0-11 age, 11+.

    The data above I want to both be calculated so how many New Births in N or M (any code with N "N*", are there. No mater what the number is)
    with in a specific date range ie 1/4/14-30/6/14, which I know would equal QTR 1.

    The question I want to ask are:
    How many New Births in N (Newtown) were there in QTR 1 (1/4/14-30/6/14)

    I ideally this new data I would like to be linked to a new work book, but that calculates and updates when the data changes in work book 1.

    Many thanks in advance

  53. Lydia Noble says:

    I have work book 1 which collates a range of data.
    Column A - codes N2, N17, M4, M2 etc(only N and M are used, although the following numbers vary)

    Column C - New Birth, 0-4 years, 5+ years

    Column F - shows dates of data entry.

    My question to ask is:

    How many N or M (column a) have Newbirths (Column c) between the dates 1/4/14 to 30/6/14 using the dates in column F
    Then how many N,M 0-4 yrs in above dates etc
    There will also be 3 further date ranges, 01/07/14 to 30/09/14, 01/10/14 to 31/12/14 and finally 01/01/15 to 31/3/15.
    hope this makes sense

    Ideally I would like to ask additional questions for calculations possibly in a new work book, but with all data linked so it would be constantly updated. I have briefly learnt how to link 2 workbooks together.

  54. Hi Lydia,

    Your second comment made the task clearer.

    Since you have to sum by several conditions you will have to use the COUNTIFS function rather than COUNTIF. The formula can be as follows

    =COUNTIFS(A:A, F2&"*", B:B, F3, C:C, ">="&F4, C:C, "<="&F5)

    Where:

    F2 - the cell containing either N or M
    F3 - the cell with "New Birth" etc.
    F4 - the sell with the "start date", e.g. 1/4/14
    F5 - the cell with the "end date", e.g. 30/6/14

    Naturally, you can put the above conditions directly into the formula, but in this case you will have to re-write it for each set of conditions.

    You can also use the above formula in another worksheet, by adding the worksheet's name before the ranges, like this:

    =COUNTIFS([Book1.xlsx]Sheet1!$A:$A,F2, [Book1.xlsx]Sheet1!B:B,F3, [Book1.xlsx]Sheet1!C:C,">="&F4, [Book1.xlsx]Sheet1!C:C,"<="&F5)

    • Lydia+Noble says:

      Thank you so much Svetlana, you are a a life saver, thank you so much for your time. I am trying the first part then I will venture on to the answers going in to another worksheet. But I am guessing or hoping IO can use the paste link to do this! Thanks again

  55. Steve says:

    Hi there-
    I was wondering if I can use the COUNTIF function with a very specific conditional test. I have a set of data by date where for one date in column A, there might be 4 corresponding numbers in column B. Example:

    9/8/2013 43
    9/8/2013 45
    9/8/2013 36

    Is there a way to use COUNTIF to only count the appearance of a number greater than x once per date? For the above, I would want it to only count once if the criteria were ">35".

    Thanks,

    Steve

  56. jai says:

    Hi there

    I want my excel spreadsheet to count letters in a row but with a unique condition behind each letter i.e A=1, B=2, C=3, D=4, E=5, F=6 G=7

    so if the cell in row F has b and row G has E total will 7

    is there a way to do this?

    jai

  57. Matt says:

    I have an excel spreadsheet that lists the different subjects that a student has undertaken.

    Example (cells N2:N9):

    Information Technology
    Physics
    Advanced Maths
    General Maths
    English Research
    English Critical
    English Academic
    Computer Applications

    I would like to insert a formula underneath this (in cell N10), to look at the subjects the student has undertaken, and return a result based on this search. I need to identify if a student has undertaken all 3 English subjects listed. I have tried multiple formulas, but cannot seem to get the right outcome, as I am searching on a range of cells (N2:N9).

    Thanks

    Matt

  58. Steven says:

    I'm having an issue with compiling certain data.

    I'm trying to find an overall percentage of participating users for certain questionnaires they have answered. For example, I have:

    =(COUNTIF('Sheet 1'!$C4:$Z4, "*CHI*")+COUNTIF('Sheet 2'!$C4:$Z4, "*CHI*")+COUNTIF...and so on for 32 sheets.

    While that's fine and all for those who participated in each questionnaire: ...+COUNTIF('Sheet 32'!$C4:$Z4, "*CHI*"))/(COUNTIF('Sheet 1'!$C$1:$Z$1, "*CHI*")+COUNTIF(...up to sheet 32.

    $C$1:$Z$1 being my headline, line 4 being this particular individual's responses (where "CHI" might not exist in certain sheets)

    The issue I am having is that there are many individuals who couldn't complete all the questionnaires (we're talking hundreds). I don't want to have to go sheet by sheet, line by line to find and specifically eliminate which sheets they didn't complete to get the proper percentage (they didn't do sheets 12, 14, 20-22, 25 for example, and I would have to delete those COUNTIF segments from the formula, just for that one cell so those extra 6 aren't mistakingly reducing the individual's percentage).

    Is there an IF or COUNTIF formula that can count an entire row only if there is a timestamp (proof they filled the questionnaire) next to the individual's name?

  59. Omar says:

    I want to count how many names appear in range C5:C11 from a list of names that I have. That list I use in drop down menus in the range C5:C11. My names are Reid, Tyrone, Chris, and Paul. I can select those names from the drop down menu. I want to count how many names from the list are in that range. I tried countifs with not sucess

  60. Meg says:

    Hi - I'm trying to compare two worksheets to see if an email address on sheet 1 is in a range of cells in Column A in sheet 2. I've tried using vlookup and ifcount formulas but neither are working. I removed all formatting from both sheets and I'm using Excel 2013. Can you please suggest a formula or point me in the right direction? Thank you!!

  61. Kimball High says:

    Just wanted to say a big THANK YOU for this article. Buried in the article is a short paragraph about how to use the symbols as text in a countif(s) and it has really helped. Thank you for taking the time to publish this comprehensive explanation.

  62. ALi says:

    need to distinct sum ,distinct count,Distinct Average

    values in cell can be blank.

    if have found two way to do distinct count but could not found how i can do the other
    =SUMPRODUCT((A3:A21"")/COUNTIF(A3:A21,A3:A21&""))

    =COUNT(IF(FREQUENCY(B3:B21,B3:B21)>0,B3:B21))

  63. Willie Alvarez says:

    I have a very long table. This is just an example of the table:

    A1: Joe

    6:00 am 7:00 am 8:00 am 9:00 am

    User Date Activity Description
    Joe 11/4/14 6:10:08 am Build Build castle
    Joe 11/4/14 6:10:10 am Build Build castle
    Joe 11/4/14 6:15:08 am Build Build castle
    Joe 11/4/14 6:30:08 am Build Build castle
    Joe 11/4/14 6:39:08 am Build Build castle
    Joe 11/4/14 7:10:26 am Build Build castle
    Joe 11/4/14 7:20:39 am Build Build castle
    Joe 11/4/14 7:25:26 am Build Build castle
    Joe 11/4/14 7:50:39 am Build Build castle
    Robert 11/4/14 8:00:00 am Build Build castle
    Robert 11/4/14 8:10:08 am Build Build castle
    Robert 11/4/14 8:15:00 am Build Build castle
    Robert 11/4/14 8:20:08 am Build Build castle

    I need to find a way for excel to give me how many time gaps are greater than 10 minutes between 6am and 7am, and input that in one cell. Also, I need in another cell the total time of those 10 minute gaps for that same hour. I need these calculations only when a specified user is entered in cell A1.
    Can you help me with this?
    Thanks, Willie

  64. liam says:

    Hi im wanting text "IN" to equal 2 when typed in how do i use countif to add 2 toa total amount

    thanks Liam

  65. John says:

    Hi,

    Please help
    I have data this

    Folder Pax
    TU1 2
    TU1 3
    TU2 1
    TU2 5
    TU3 6
    TU3 1

    And I want
    Folder Pax
    TU1 5
    TU2 6
    TU3 7

  66. Mat says:

    I am trying to do something much simpler. I would like to count the number of different items in a list and just by specifying the column as a range. Can this be done?

    • Mat says:

      The closest I have got is

      =SUMPRODUCT(1/COUNTIF('Sheet1'!A1:A8000,'Sheet1'!A1:A8593))

      Where there are 8000 lines in the spreadsheet. However if I put in 8001, I get a divide by zero error. It would also be preferable if I could ignore the top line as it is headings but I can jus -1 if needed

  67. Rachana says:

    Hi Svetlana,

    I have a main summary sheet where i want the count meeting below criteria in a cell.

    I have sheet1 and summary sheet. Sheet1 has Col F with all the names of the officer like Tina, Mira etc. Col I has months like March,April etc. And Col J has the target dates of visit after 6 weeks from the tenancy date. This col J also has the date in different year 2013, 2015,2016 so on, e.g. 01/05/2014, 31/03/2015 etc.

    Now from sheet1, a cell in summary sheet should check for the names from Col F e.g. Tina, then it should take count all the April Months from Col I against Tina's name, and should count only the dates that fall between 01/04/2014 till 31/03/2015 against Tina's name.

    1) To select the names from F col from sheet1
    2) to count months against a name from col I
    3) to add a condition that the visit target date - 6 week which is in col J should fall from 01/04/2014 to 31/03/2015 against the selected name

    Please can you advise a formula for this.

    Thanks in advance,
    Rachana Ranpura

  68. Richard Phillips says:

    I have a Question and its probably easy for you to work out, but it's bugging my head out.. Ok, I have some Data and its in columns Based format running down one column each L, A, B, each l,a,b, if that makes sense running down the Spreadsheet is, the L value is Averaged out and each L ,A, b,value is posted to each other Cell averaged out.

    Each L,B,A Value is averaged out,OK. The value is posted to another Cell and the Cell calculates a Positive value, Greater Than Positive or a Negative, the worked out Value is posted to another cell. The then value is Dynamic, meaning its not the same based on Human input moving a threshold up or down. It reproduces a value thats not constant until a actual result is put into the formula Cell.. I want it to collect a value from a cell with a formula thats Dynamic....

    I want to work out a Greater than and less on this Dynamic Cell. But its producing a Zero Result this Dynamic.
    If at all you need a video or screen representation then i will look at that option..

  69. courtney says:

    Hi Svetlana,

    I am trying to count the number of cells in a column which are all populated with text. I am trying to count the amount of cells in that column which contain less than 10 words? how do i do this?

    Many thanks,

    Courtney

  70. Wayne says:

    I have a worksheet that currently counts the number of rows in a worksheet that are populated in certain columns:
    =COUNTIFS(Andrew!E2:E998,"*",Andrew!F2:F998,"*",Andrew!H2:H998,"*",Andrew!J2:J998,"*")

    this method of conting is used to count for each tab in the workbook and is sumarised with the formula: =SUM(F20:N20) to produce an overall value.

    There are 5 of these sheets that are linked into a master sheet that counts the =SUM(F20:N20) from each book and produces a grand total.

    Is there a function that will allow me to produce a list of rows that satisfy the formula buy way of a report?

  71. ameya says:

    Hello Svetlana and everyone,
    I had a small question regarding data analysis. I tried looking COUNTIF and FREQUENCY. I want to quantify following things in my data set
    I want measure the number of events where 0 appeared 5 times or more, consecutively in a given data set. (e.g.: 000001100010000000 (here zero appeared consecutively, twice, 0=5 and 0=7))
    Is there any formula to do this?
    Thanks in advance
    Cheers
    Ameya

  72. Sam says:

    Hello,

    Thank you for this post that I must admit is very useful for people introducing Excel. I would like to know something, I'm trying to put out duplicates from a column but the thing is, by adding all the data I'd like the formula to count cells from number (scale number) 402 to 460 and so on for 502 to 560 etc.

    Here is a look on the formula :

    =SUM(IF(FREQUENCY(MATCH(A2:A749,A2:A749,0),MATCH(A2:A749,A2:A749,0))>0,1))

    and then this is what i'd like to add inside the previous one :

    =COUNTIF(A2:A749,">=502")-COUNTIF(A2:A749,">560")

    A2:A749 datas are numbers ONLY

    To sum up, I want to count all the cells except duplicates and only from number 502 to 560

    Thank you for your advice!

    Sam

  73. Corinna Griffin says:

    OMG! Thank you!!! This helped out so much!

  74. Frank Johnson says:

    Thank you so much for taking the time to share your knowledge! I was wondering, is there a way to calculate the % of numbers within a column that are greater than another value? I work in education, and I have very long lists of students names and their scores on any given assessment. I would like to, for example, calculate the percentage of students who scored greater than or equal to 42%. Is this possible? I can sort and do the calculations manually, but the larger my lists get, the more errors I have. Thank you!!!
    A B
    Bill 24%
    Chris 82%
    Ringo 19%
    Paul 96%
    John 94%
    George 88%
    Mick 16%

  75. Jane says:

    =COUNTIF(tblJanuary[@[1]:[31]],"V")+COUNTIF(tblJanuary[@[1]:[31]],"A")+COUNTIF(tblJanuary[@[1]:[31]],"S")+COUNTIF(tblJanuary[@[1]:[31]],"H")

    I want to count the number of certain values (V, A, S, and/or H) in row, but I do not want it to count any other values I might enter. I tried the above formula but it still counted if I enter something other than V, A, S, H. Any ideas?

  76. Chad says:

    =AND(COUNTIF($A$1:$P$261,A1)>1,A1>0)

    Good day,

    I am trying to highlight all duplicate dates that match on a worksheet, the above formula is a conditional format that works, but it also highlights text values.

    I originally wanted to create a conditional format that referenced a column on another worksheet(called A), and then highlighted those dates that corresponded in a table on worksheet(called B). But I could not get it to ignore blank cells or cells with text in them. I tried many, many, times.

    I am probably too much of a novice in my understanding of how formulas work.

    Any help you can offer would be very much appreciated.

  77. varigonda gopi says:

    hi Svetlana,
    how to count cells by identification of the cell color.

  78. Anonymous says:

    Hi, below is my excel sheet , can we write a power query to find number of session( start to End)
    Expected result for below excel is 2 , if suppose A12 had End Session the count to be 3.

    A
    1 Start session
    2 ...
    3 ...
    4 ...
    5 End Session
    6 Start session
    7 ...
    8 ...
    9 End Session
    10 Start session
    11 ...
    12
    13
    I do have a huge data in working excel (taking lot of time using vba script ). please help to find a solution.
    Thanks in advance,
    Sunil

  79. Sunil M P says:

    Hi, below is my excel sheet , can we write a power query to find number of session( start to End)
    Expected result for below excel is 2 , if suppose A12 had End Session the count to be 3.

    A
    1 Start session
    2 ...
    3 ...
    4 ...
    5 End Session
    6 Start session
    7 ...
    8 ...
    9 End Session
    10 Start session
    11 ...
    12

    I do have a huge data in working excel (taking lot of time using vba script ). please help to find a solution.
    Thanks in advance,
    Sunil

  80. Sam says:

    Hello, I am making a spreadsheet for a sales team. I have the formulas to count the deals they have. I have salesperson 1 (column I) and salesperson 2 (column j). My formula is ((countif(i5:i29,"mike")+(countif(j5:j29,"mike").

    However, when there is a salesperson two, then both sales person 1 and salesperson 2 are
    Only allowed a "half deal" meaning it should
    Count .5 for both rather than full.

    Can you help?

    Thank you so much

  81. jo says:

    i have got many sheets in my workbook.but the problem is that i have to create pivot tables of only those sheets which end with Total??? Please help please ---Thanks in advance

  82. Hoda Ghalayini says:

    I want to know how to use the COUNTIF Formula if we want to count number of customers whose second letter of their names is as "A"

  83. Sujatha Victor says:

    Very useful for analyzing the marks scored by the students. I want a formula (countif function) for finding the marks scored by the students from 1 to 4 (like that 5 to 17; 18 to 24; 25 to29; 30 to34; 35 to 44;45 to 49). Range is marks scored by students for 50 marks. I kindly request you to please intimate the criteria for which I will be ever grateful.
    Regards,
    Sujatha Victor

  84. Rachana says:

    Hi Svetlana,

    Very happy new year to you!!

    Please can you help me on below query.

    I have a table to calculate balance leaves each time number of leaves taken are entered it would deduct from the available no of leaves for the period. Now i am trying to apply the formula =SUM($K$8-G12) to the whole column M. Now i want to add a condition that if the Col G is blank, then M should also have blank value otherwise it would count as per formula =SUM($K$8-G12) which is Total no of leaves - leaves taken = balance leaves.

    Thanks,
    Rachana

  85. Lissa says:

    I'm trying to count the output of an IF function. It seems that the COUNTIF function can't read the output of the IF function as data, but only the function behind the output. Is this true? Can I use COUNTIF to count output form an IF formula?

    Thanks,

    Lissa

  86. Pauline says:

    Hi i need to count the sum of the failed but the problem in it i that one column there are two different data i mean on the same day
    thanks

  87. Ulvi says:

    Hello,

    I want formula like this:

    for ex : if row 4 have value in anywhere will find and then take text or value from upper row's - example formula found f4 value then will take f3 text or value...

    Thanks

  88. fiza says:

    Hi, i want to use countifs function , to assign rank to the people

    FOr exp.

    Column A: List Emp Name
    Colum B: contains Numeric figures achieved by them
    Column C: in this i want Rank( high to low), means the employee got the highest number in the list, should be ranked as "1", as he is the topper.

    Can i use it for this ?

  89. ChrisB says:

    On a yearly spreadsheet, I have numerous entries with dates throughout the year. If I want a count of how many entries there are for a given month, January, February, etc., how would I do that? Also, the entries are on one worksheet and the count will be on a different worksheet (in the same workbook), how would that formula be written?
    For example:
    1/10/15
    1/18/15
    2/17/15
    3/4/15
    Count=2 (for January)

  90. king9x says:

    I'm trying to count non duplicate using
    =SUMPRODUCT((COUNTIF(L13:L567,L13:L567)=1)*(L13:L567""))

    but when I filter other column to unique name, the total remaining the same. How it will be possible by using same formula? Thanks.

  91. Jp says:

    Need some help here I have analyzed 10 Columns of 190 numbers and have counted 321 matches I want to take the 321 numbers scattered throughout the various columns and put them in another column in order of how they appeared. Any idea how I could lookup, find and get the data into a new column? The thing is as new data goes in the numbers of matches can vary between 1 and 1900

    Thanks

  92. GR says:

    Hi
    I want to be able to do a COUNTIF across 2 spread sheets but for 1 cell in both sheets, so D2 in one spread sheet will be the same as D2 in the other. However, I want to use the count if option to do this. So if D2 was blank in one sheet, it would be blank in the second sheet, but if D2=2 in the first sheet then it would be 2 in the second.
    Currently the first sheet will be blank but the second sheet will be 0 as it is linked to the first sheet. But we need this to be blank.
    Hope this makes sense, thank you.

  93. Dee says:

    Hi - hoping you can help.

    I need to count column B (which are various ID numbers), remove the duplicates but only count them if Column A says "BIP" and Column F says "Active"

    Is it posible to do this in one formula??

    Thanks heaps

  94. Hailey says:

    Hello!
    Im trying to count blank cells in a column in my spreadsheet, but it wants to count the whole sheet and not just the ones included in my table. How can i do it so it only counts blanks that are part of my table (they have other information in their rows). Any suggestions on how to do this?
    Thank you!
    Hailey

  95. Donna says:

    Hi Svetlana,
    Thank you for your informative post and support! I dates in 2 columns: one column (M) for "original planned date of project completion", and another column (N) with "latest planned date of project completion". I want to count the projects that have a latest date later than the original planned date, i.e. "count if the date in column M is greater than the date in column N" specifically trying this formula COUNTIF(M3:M22,">L3:L22"). It is not calculating correctly - any ideas?
    Thanks again!!!

    • Hi Donna,

      The COUNTIF formula cannot help in this case because you need to compare 2 ranges. Try the following array formula (remember to press Ctrl+Shift+Enter) to enter it correctly:

      =SUM((M3:M22>L3:L22)*1)

  96. Twister says:

    Hi I am trying to count the number of clients within ranges.
    example:
    all clients who are >= 10 but =10")and=COUNTIF(H3:H1659,"20")

  97. KusumP says:

    Hi there

    I am trying to find out a way to count the dates in multiple cell range : in My excel sheet

    30-Sep-14
    30-Dec-14
    30-Jul-14
    30-Jul-14
    30-Jul-14
    30-Jul-14
    30-Sep-14
    30-Aug-14
    30-Aug-14
    30-Dec-14
    30-Dec-14
    30-Dec-14
    7-Oct-1
    30-Sep-14
    30-Dec-14
    30-Dec-14
    30-Jul-14
    30-Dec-14
    7-Oct-14
    there are dates in column range O3:O46,L3:L47,R3:R51,U3:U51,X3:X50

    i am trying to get the count of date which fall in within particular months i.e. April 14, May 14,June 14.July 14.August 14,September 14.....

    i am using this formula but not getting any result, thanks in advance for any help.

    =COUNTIFS(O3:O46,L3:L47,R3:R51,U3:U51,X3:X50,">=1/1/2013", O3:O46,L3:L47,R3:R51,U3:U51,X3:X50,"<=31/1/2013")

  98. RandyS says:

    I have the exact same problem as Donna (Q95). Essentially, I'm trying to get a single total of the number of projects where the actual completion date (column B, for example) exceeds the proposed completion date (column A). I’ve tried various formulas, including the one Donna noted, with no success. Any help would be greatly appreciated.

  99. hmanji89 says:

    Hi, I have a workbook with about 6 worksheets.
    Each worksheet has to be filled with various values name age ....
    the last worksheet is a 'report worksheet' that has to pick up input data per month from the other worksheets, undergo some formulas and then display the result.
    This is all fine.

    My problem is the other worksheets are filled in on a daily basis on the same sheet over many months BUT the report has to dis-aggregate the data by months to be input into the report. n.b. There is a month column.

    Now if i have a formula such as =COUNTIF('SHEET1'!AF9:AF608,"FEMALE")
    how can i manipulate the formula into picking up the no. of 'females' in 'sheet1' in march only
    n.b. there is a seperate 'report worksheet' for each month.

  100. Bharat says:

    hi Svetlana Cheusheva..

    I think you are a MVP.

    I had gone through the your website and found that you have helped alot of people in solving excel problems. .

    I really appreciate your efforts.

    I want to be excel expert like you tell me what should I do for that...

    God bless you..

    • Hello Bharat,

      Thank you very much for your kind words. No, I am not an MVP, but I am lucky to work with very talented and knowledgeable people and I've learned a lot from them. Reading also helps : )

  101. Sanjeev Kumar says:

    You're really great!!

  102. John Vaught says:

    Hello i am having a Count if issue

    the formula i am using is:
    =OFFSET('Raw Data'!$K$5,1,0,COUNTIF('Raw Data'!$K:$K,">0"))

    i am using this in name manager to automatically update a control chart as i dump data in my "Raw Data" tab.

    the chart is a representation of a shipments date (x axis) and the analysis of the shipment on that date.

    the issue that i am having is that the formula is not counting the entire column. Furthermore my chart is still picking up the zeros

    there is a large amount of entries in the column (J5-J65 the formula above stops at J26. i can not figure out why.

  103. Zenher says:

    Hi there,

    Good day to you, i have a XLS with 2 Tab Aging and score card. I have different value for column "W" in aging tab and i would like calculate the below.

    1. to calculate the total for all the different texts which are not "abc" and with a specific aging day.

    ** Please Help**

    2. to calculate the total "abc" and with a specific aging day, which i mange to do it.

    =COUNTIFS(Aging!$W:$W,"abc",Aging!$B:$B,ScoreCard!J18,Aging!$N:$N,"<15")

  104. Ashish baddat says:

    Hi Svetlana - Hope you are fine, required small help from you, is there any formula to capture code from statment by using if formula. I have 34 Codes, need to capture below code if they come in different entries, Example for entry : XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX & /AC04/ XXXXXXXXXXXXXXXXXXX, in this case AC04 is code. want to capture below code by using if formula, they can start AC or AM or BE or AG or BT or MS or PY or RS.

    1 AC01
    2 AC02
    3 AC03
    4 AC04
    5 AC05
    6 AC06
    7 AM01
    8 AM02
    9 AM03
    10 AM04
    11 AM05
    12 AM06
    13 AM07
    14 AM08
    15 BE01
    16 BE02
    17 BE03
    18 BE04
    19 BE05
    20 AG01
    21 AG02
    22 DT01
    23 MS01
    24 PY01
    25 RF01
    26 RC01
    27 RC02
    28 RC03
    29 RC04
    30 RR01
    31 RR02
    32 RR03
    33 TM01
    34 X1!c2!n

    Please can you advise a formula for this.

    Thanks & Regards
    Ashish Baddar

  105. Nishant says:

    I have two columns. I would like to count the the no. of values in the right column greater greater than the corresponding values in left column. How to accomplish this?

  106. Roman says:

    Hello,
    I need help with function Countif, so:
    I want to count two cells for example: if cell A1=1 or cell A2=1 so count as 1, if one of these cells equal 1, count as 1, if both cells equal 1, count 1 as well. How is it possible to do with function Countif or with other function?

    Thank you in advance

  107. Kristy says:

    Hi,

    I want to format Column A of a sheet. Column A has names and in columns B:M are the 12 numbers (1-75) associated to each name. In Q1:Z30 I have up to (depending on the day) 300 random numbers 1-75. I need the name in Column A to turn Pink when 9 of the numbers match the numbers entered in Q1:Z30. Since there are up to 300 numbers entered in Q1:Z30, some numbers are entered more than once. Here is the Conditional Formatting formula that I am using that isn't working:

    =SUM(COUNTIF($B1:$M1,$Q$1:$Z$30))>=9

    The formula is counting each instance that it matches and adding those up, and when those 12 numbers have been matched 9 times it highlights the name in Column A. I need the formula to highlight when 9 of the numbers in B:M have been matched period, not how many times they have been matched.

    Thank you so much for your time!

  108. jen says:

    if any cell in column b equals a certain text then count cells in column c

    post jan feb mar apr may
    admin 10 20
    admin 25 2
    asst manager 10 17
    asst manager 2 28
    bartender 21
    bartender 30
    captain 15 15
    captain 13 17
    hostess 10 15
    hostess 25 5
    manager 16 14
    manager 30
    manager 25 3
    manager 20 2
    supervisor 20 10
    supervisor 15 15
    waiter 25
    waiter 30
    waiter 20
    waiter 18
    waiter 10
    waiter 14 13
    waiter 20
    waiter 25
    waiter 28
    waiter 30
    waiter 15
    8 8 11 10 4

    admin 1
    asst manager 1
    bartender 1
    captain 0
    hostess 1
    manager 1
    supervisor 1
    waiter 2
    Total 8 0 0 0 0

  109. Adam says:

    I am trying to count how may times Toronto plays a game in the U10 age group.

    Column A = Age group
    Column B = Home Team
    Column C = vs
    Column D = Away Team

    Age Group Team 1 Team 2
    U8 Moncton vs Halifax
    U9 Toronto vs Moncton
    U10 Moncton vs Halifax
    U12 Halifax vs Vancouver
    U8 Toronto vs Vancouver
    U9 Halifax vs Toronto
    U10 Toronto vs Vancouver
    U12 Vancouver vs Moncton
    U8 Vancouver vs Halifax
    U9 Toronto vs Moncton
    U10 Moncton vs Vancouver
    U12 Vancouver vs Moncton
    U8 Vancouver vs Toronto
    U9 Moncton vs Vancouver
    U10 Toronto vs Moncton
    U12 Halifax vs Fredericton

  110. Athi says:

    Hi
    I want to know whether it is possible to get the count of cells that are not empty.

  111. Shoaib Hunerkar says:

    how to count a data by date but including text contain like

    A columns
    1-Mar-15
    6-Mar-15
    11-Mar-15
    16-Mar-15
    21-Mar-15
    26-Mar-15
    31-Mar-15
    a
    a

    how do i take all the count

    • Hi Shoaib,

      Not sure if I understand the task correctly. Anyway, if you want to count all cells with any data in column A, you can use the following formula:
      =COUNTIF(A2:A100,"<>"&"")

      Please see the example in the "Count if blank or not blank" for full details.

  112. Sean Lim says:

    Hi,

    I need to count the number of hours for midnight charge given the criteria to be 11pm to 6am.

    E.g.(1) Cell A1 9pm and cell B1 9am. the result for midnight hours should be 7hrs
    E.g.(2) Cell B1 12am and cell B2 10am. the result for midnight hours should be 6hrs.

    How should i input the formula?

    thank you very much.

  113. Milkesh says:

    I want to know whether its possible to calculate and if yes what are the tags to find out whether the values have crossed the certain threshold and if it does can the names of whoever has crossed can be displayed

  114. Elena Nikolova says:

    Hi Svetlana,

    Thank you for this very helpful topic!

    I have this question: Is it possible with a formula to count all the rows from A to D in which the sum of the cells is greater than 0 and between 4.

    For example in this case, the result will be 2 :

    A B C D E
    1 0 1 5 0 4
    2 0 3 12 0 0
    3 0 1 2 0 1
    4 7 2 5 0 2
    5 0 0 0 1 4

    Thank you very much,

    Elena

    • Hi Elena,

      Sorry, I am a bit confused. Do you mean the sum of values in columns A through D in each row, e.g. A1:D1, A2:D2, etc.? If so, it is always greater than 0 and even greater than 4 in your example. Please clarify.

  115. Ricky says:

    Hi Svetlana,

    Thank you for publishing this page and always being active to respond.

    I am stuck with a Problem :

    Query : Count of projects between 01/01/2015 and 01/31/2015 whose status is ABC OR XYZ.

    From your forum I could do it for Count of project Less than 01/31/2015 but that will include all 2014 and 2013 projects also.
    I used the following formula for this :
    =COUNTIFS(Details!D17:D33,"<01/31/2015", Details!E17:E33, "ABC")+COUNTIFS(Details!D17:D33,"<12/31/2014", Details!E17:E33, "XYZ")

    Could you please help out with date range for this.

  116. Sat says:

    Hi,

    Is there any way we can figure out a formulae for this tracker. Basically tracker should automatically calculate the blood samples drawn 12 months from the day they signed the consent form.

    Eg- if patient signed consent in 27/07/2013 and the bloods are drawn until end of JULY 2014, the tracker should calculate signed consent + 365 days.

    Thanks a lot in advance.

    • Hi Sat,

      I think it is possible. For me to be able to suggest a formula, please explain the structure of your data, i.e. what columns contain signed consent dates and blood drown dates and probably patients' names. Also, what exactly you want to get as a result - the expiry date for each blood sample or something different?

  117. Dan says:

    Hey, great post, and thanks for the pointers! I have a basic timesheet for work and would like to count the days spent on each project. Are you able to tell me how I can redirect the condition to another cell? So that:

    =COUNTIF(timesheet!$C$4:$G$500,"project A")

    would become:

    =COUNTIF(timesheet!$C$4:$G$500,"cell reference that redirects to the beginning of the row, which is where the project is named")

    This way, I will be able to remove the need to enter the project name twice and the risk of an error due to billing days for project A against project B.

    Thanks!

  118. Ben says:

    Hi Svetlana,

    I am trying to figure out how to formulate a countif function where I am trying to measure exposure on certain loans on any given date. I have a bunch of loans, the amount pertaining to that loan and the respective maturity/expiry date. Essentially, I am trying to measure peak exposure from today lookng forward over the next 5 years so that I can create a graph in excel illustrating exposure over time. I would like the graph to show me how much exposure I have to those loans in year 3 in quarter 4 and how many have been repaid.

    Do you have any idea how I might do so?

    Many thanks in advance,

    Ben

  119. Muhamed Rayees says:

    Hi Svetlana Cheusheva

    hope your doing well.. please help me out .. i have attached my worksheet with this mail.

    i want total value of cells A , B , & C to be calculated in Cell "D" automatically .

    please show me step by step..

    TERMINAL

    PRICE

    A

    1000

    B

    2000

    TICKET

    PRICE

    YES

    0

    NO

    150

    STAGGING

    PRICE

    STAGE-1

    510

    STAGE-2

    510

    PARKING

    0

    your support will be highly appreciated

  120. Calin says:

    Hello Svetlana,

    I need help with the following problem. I have a column with 4 different values:

    Column A
    39
    19
    20
    0
    20
    19
    19
    19
    0
    39
    19
    0
    39
    19
    39
    0
    0
    0
    19
    39
    20
    0
    20

    I need a formula that will count the number of appearances of the number 0 since the last time the number 39 has appeared. Numbers 19 and 20 are not important, but they have to be there. Basically it counts the number of times the lowest value in the column has appeared since the last appearance of the greatest value in the column. The formula will be put in column B and will have to show the following results:

    Column B

    0
    0
    0
    1
    1
    1
    1
    1
    2
    0
    0
    1
    0
    0
    0
    1
    2
    3
    3
    0
    0
    1
    1

    Is it possible?

    Thanks in advance!

  121. Lidiya says:

    Hi,

    I was wondering if it is possible to count partial matches in the following example:
    Orange juice
    Apple juice
    Juice
    Coca Cola

    So would want the count if formula return 3. I believe if I use *juice it will count only the first two, as "Juice" doesn't have anything in front of it.

    Thanks!

    • Hi Lidiya,

      You can use *Juice* and it will count all 3. The asterisk tells the formula to count all instances regardless of the presence or absence of any other characters in front of / after the word.

  122. Help says:

    I have a table with numbers in Columns F, G, H, I, and J. There is an identifier in Column B.

    I need the average of all numbers in Columns F, G, H, I, and J where the identifier in Column B = A14 (i.e. the value in A14).

    I tried the following:

    =AVERAGEIFS('Hours-Backup'!F3:J1048576,'Hours-Backup'!B:B,'Group Dashboard'!A14)

    This results in a #VALUE! error.

    I can't figure out what the problem is. Any help would be appreciated.

    Ultimately, I want to make it so that the function ignores all cells that are non-numeric or blank. In other words, I don't want it to assume that blank cells are 0 for averaging purposes.

    Thanks!

  123. Taylor Etherton says:

    I have a spreadsheets that tracks the schedule of my sales rep's from week to week. I have a column set up to count how many total appointments they have per day. In this column I use the formula =COUNTIF(tblMonday[@[9:00 AM]:[5:00 PM]],"*") so it reads all addresses as a count. I have another column set up to count only the warranty appointments for that day. In that column, I am using the formula =COUNTIF(tblMonday[@[9:00 AM]:[5:00 PM]],"(W)*") to recognize that when I type (W) in front of the address, it is a warranty call and will go just to that column. I would like to add a third column that counts only the addresses that have no (W) in front. Please... send help. :-)

  124. MOHD ALAM says:

    i want to know how calculate spreadsheets
    my no is this 109|110
    109|110
    109|110

    how many times 109 in this range

  125. Poe Veasna says:

    Dear Sir/madam,

    Please help.

    I have a range of codes with some are duplicate in one column. I would like to count the code but for those are duplicate to be counted only one time.

    Veasna

  126. Lori says:

    Hi Svetlana,

    I have an excel table to keep a running balance of the decreasing loan amount, as payments are made (see excerpt from table below).
    Currently, the formula in column D is =SUM(D2,C3) & the formula in column E is =E2-D3
    The problem with this is that is when I continue the formula's all the way down to #21 on the list; the $150 in D continues all the was down the list, & the $4,350 in E does the same. It stays that way until I add a new payment to column C; and then the new total continues down the list.
    I don't want anything showing in Columns D & E until another payment is made. I want the formula's in there; so the balance automatically adjusts when I enter a payment, but I don't want any value showing if no payment has been made.
    I thought I could accomplish this by using a COUNTIF formula.
    Something like: If C is greater than zero, then =SUM(D3,C4)& =E3-D4.
    I'm not sure how to set the up.
    Can you help me with that? Any help would be very much appreciated.

    C D E
    $ AMOUNT of PMT BALANCE PD TO DATE BALANCE OWING
    $0.00 $0.00 $4,500.00
    $150.00 $150.00 $4,350.00

  127. Top Uniforms says:

    Hi Svetlana,

    Can you help please?

    I am currently using the CountA function to count the cells in a column.
    Some cells have ? or * in them.
    I have the formula to not count these cells?
    How do I achieve this?

    Thanks much.

  128. Keith says:

    I am trying to keep track of attendance of employees for the current month. I have a formula that will calculate the last 30 days for the attendance code, but instead of last 30 days I would like for current month only:

    =COUNTIFS(Attendance!$B:$B,$B5,Attendance!$E:$E,"CIO - CI OUT",Attendance!$C:$C,">"&(TODAY()-30))

    I have 2 separate sheets - Attendance sheet which is the sheet I log attendance to, and the other sheet where I am placing my formulas on. Here is a breakdown of everything from that formula above

    Counts for that employee if it meets all of the following criteria:
    **Attendance!$B:$B,$B5 >> Employee Name (want to count specific employee)
    **Attendance!$E:$E,"CIO - CI OUT" >> Attendance code (want to count specific attendance code)
    **Attendance!$C:$C,">"&(TODAY()-30) >> THIS IS WHAT I NEED TO CHANGE. I need to change instead of being last 30 days, to be for the CURRENT month only.

    So today being in May, I want it to count how many times "John Doe" has had the Attendance code of "CIO - CI OUT" for THIS MONTH. It should reset next month to only include June automatically.

  129. Debbie says:

    Hi Svetlana,
    I am using COUNTIF to give me a count of results that are in a range. I want to know how many fall between 8,000 to 9,000, 9,000 to 10,000, and so on. My range of data to look at is A1-A164 but within that range are many subcategories with totals for that category. My COUNTIF counts the total in the results but I don't want the totals counted. Other than making 50 separate ranges is there any other way you can think of to exclude the total cells from being included in the count?

    Thank you for any help you can offer.

    • Hi Debbie,

      I am afraid I cannot figure out any way other than you suggested :(

    • David Liska says:

      Debbie,
      I realize it has been some time since if you inquired; my apologies. If I'm understanding your dilemma properly, is it not possible to use COUNTIFS and ignore rows where the total values have the keyword "Total" in an adjacent column?

  130. Henry says:

    I have summation formulas in column Z and 2 cells with a value > 0
    When I use the COUNTIF(Z5:Z108,">0" ) it correctly answers 2
    How can I combine that with the question "How many are SHOWN that have a value > 0 somewhat like =Subtotal(109,Z5:Z108)

    Unsuccessfully tried
    SUBTOTAL(109,COUNTIF(Z5:Z108,">0")) and
    COUNTIF(SUBTOTAL(109,Z5:Z108),">0")

  131. Johan Bolle says:

    Svetlana,

    I'm trying to make a formula that does not count any cell that is Blank or 0. What am I missing here? "=-COUNTIF(B5:F5,B6:F6,"<=0"),(B5*B6+C5*C6+D5*D6+E5*E6*F5*F6)/G5" The B5..F6 cells could be blank or 0, but I just want a simple formula that does provides the weighted average.

    Thank You!

    Johan Bolle

  132. Jomare Bryan says:

    Hi Miss Svetlana how i could count the total sum of all tools without using pivot example.
    Colum A. Colum B.
    Description: Total Amount:
    Tools & Equipment A 3,000.00
    Tools & Equipment B 4,000.00
    Tools & Equipment B 1,000,00
    Tools & Equipment C 2,500,00
    Tools & Equipment D 500,00
    Tools & Equipment C 1,500.00
    Tools & Equipment E 800.00
    Tools & Equipment A 3,800.00
    Tools & Equipment E 540.00
    Tools & Equipment E 900.00
    Tools & Equipment E 100.00
    Tools & Equipment E 100.00
    Tools & Equipment E 300.00
    Tools & Equipment B 6,000.00

    Can u help me pls.......

  133. Kendra says:

    Hi - I have a baseball schedule and trying to see how many times each team has a early and late game. I have used the countifs but can't get it to work.
    Away Team # Vs Home Team # Time and Diamond
    1 vs 2 715 P
    3 vs 4 845 P
    5 vs 6 715 D1
    7 vs 8 845 D2
    11 vs 12 715 D1
    9 vs 10 845 D2
    12 vs 1 715 P
    10 vs 3 845 P
    8 vs 5 715 D1
    6 vs 7 845 D2
    4 vs 9 715 D1
    2 vs 11 845 D2
    Thank you, Kendra

  134. Joe says:

    Hi, I have a spread sheet where I have a resident name in column A and a hall number in column B. There are multiple residents on the same hall, and sometimes the resident is listed more than once. I want to count the number of times a hall is listed, but no duplicates(i.e Joe 100
    Bill 200
    Joe 100
    Chris 300
    Maple 100. So I would want to find out how many times 100 is involved without the duplicate so the count should be 2. Can you help me? thanks,Joe

  135. Angelina says:

    Hello,

    I have the following data:

    Column A Column B Column C
    Estimate Percentage Percentage Expected
    (days) Complete Remaining Result

    0 0 Not Required
    5 25 3.25
    1 75 .25
    Not Required 0 Not Required

    So, those fields that are not a number and are equal to zero should not be calculated, but for the formula used to calculate "Percentage Remaining", it is: Column A * (Column B /100)

    So, my question is: How do I exclude the calculations that are zeros and are not numbers in Column A and Column B?

  136. Jose says:

    Hi,
    I know how to do this using PIVOT, but I have a non power user that will use a template sheet in excel and I'm trying to help him to get a count duplicates cases based on a condition.

    Case Cause Dealer
    447839 L CA
    447839 P CA
    447839 S CA
    448387 L CA
    448387 P CA
    447646 L PH
    447646 P PH
    447647 L PH
    447647 P PH
    447648 L PH
    447648 P PH

    In above example CA have 2 cases and PH have 3 cases. Using PIVOT is easy to get the result rather then calculating through transactional data but the user isn't PIVOT oriented and I tried hard to trained.

    How I can achieve above summary results using a function/complex formula.

    Anticipate thanks,

    Jose

  137. shwetha says:

    Hi,

    Can you help me out in finding the hours in an column G which is >4 <6 and =6.

    time is in 4:00:00 formate and also i need to find no 4 to 6 in same column

    Thanks
    Shwetha

  138. Adam says:

    Hi Svetlana,

    Looking for an idea for a function. I'm pulling data from one spreadsheet with account numbers assigned to different agents, and trying to compare it to the number of entries made in our sales database for these accounts. The problem is, the database entries contain the account numbers, but also contain other text/numbers as well. Any suggestions? Thank you!

    • Adam says:

      After tinkering around with it, I think the main point of my problem is getting the *value* function to work with a cell reference as the value, rather than a constant.

  139. Sooraj says:

    Hi Svetlana,

    I have some data like this in a column. If am giving formula (=COUNTIF(A1:A2000, A1)) to count the number of repetition of each number its not getting counted properly. Its taking into con sideration only few entries. May be coz character are more than 19. Is there any way to count the repetitions if the number of character in a cell is more than 19.

    89XXXXXXXXXXXXXXX44
    89XXXXXXXXXXXXXXX36
    89XXXXXXXXXXXXXXX28
    89XXXXXXXXXXXXXXX10
    89XXXXXXXXXXXXXXX02

  140. nikki says:

    Please help me to solve my assignment I dont know how...
    Count if
    A B C
    1. 10 20 15
    2. 5 25 4
    3. 3 6 2

    Count if
    1) 5-2
    2) 1-5
    3) 15-25

    • Hello Nikki,

      Here you go:

      1) 5-2 =COUNTIFS($A$1:$C$3, "<="&5, $A$1:$C$3, ">="&2)

      1) 1-5 =COUNTIFS($A$1:$C$3, "<="&5, $A$1:$C$3, ">="&1)

      1) 15-25 =COUNTIFS($A$1:$C$3, "<="&25, $A$1:$C$3, ">="&15)

  141. Murugesh says:

    Can you please give me solution for my earlier email?

  142. d says:

    Hi,
    I need to count the amount of profit-making enterprises:

    Проект* Выручка Себестоимость
    SAMA-001_ALFA 100 90
    SAMA-003_BETA 120 200
    ...

    THANK YOU

  143. Gary says:

    Hi Svetlana,

    I am using excel 2003 :(

    I am trying to count how many cells in column D contain a specific text, IF the dates in column F are greater than 6 months ago from today.

    I have tried this:

    =SUMPRODUCT((D4:D16390=D4)*(F4:F16390="<="&TODAY()-180))

    But cant seem to get it to work, just shows 0.

    Many thanks!

  144. Mat says:

    Hi Svetlana,

    I have a problem I think can be solved using this method but I cant seem to get the syntax right

    I have a list of words in 1 column that have duplicates and a date range in another column

    What I need to do is count non duplicates when the date is less then 7 days ago.

    the table would look something like this

    Apple 26/6/2015
    Apple 24/6/2015
    Pear 20/6/2015
    Pear 15/6/2015
    Pear 22/6/2015
    Orange27/6/2015

  145. Akbar G says:

    ________________
    |__a_|____b_____|
    |poo |480 menit |
    |ssp |360 menit |
    |LLLL|300 menit |
    |LLLL|300 menit |
    |____|__________|

    so the the result would be like this :

    ________________
    |__a_|____b_____|
    |poo |480 menit |
    |ssp |360 menit |
    |LLLL|600 menit |
    |____|__________|

    how to do opperation like that ? thanks before

  146. Savannah says:

    Hello,

    I am trying write a function to count the number of unique values in one column that fall in a category in another column.

    Here is a simplified example of my data:

    Order Qty Group
    1 1 1
    2 2 1
    3 1 1
    4 4 1
    5 5 2
    6 8 2
    7 2 2
    8 4 2
    9 6 2

    I want to know how many different/unique order quantities there are within group 1, group 2, etc but without designating the cell range, just the particular value (1 or 2 in this case).

    Thanks so much!

  147. Sara says:

    I am trying to count occurrences above a certain number; however, those values I want to count are not in a straight row or column. I have each company, then label, then each month in the columns. For each company in the column, I have 4 sets of information. I want to count 1 set for each company. How do I do that?
    Sample:
    Company A $ of x $50
    $ of y $12
    # of employees 49
    Company B $ of x $150
    $ of y $75
    # of employees 289

    For each company, I want a count of any time the number of employees in that column is greater than 250.

  148. Rohan Singh says:

    Nice Article!

  149. Douglas says:

    Hello,

    I have a simple two column worksheet. Column one consists of names, and column two of dates those names were given a task. I need a total that calculates how many times each name was given a task on today's date.

    So each day, as the tasks are assigned and entered the total should reflect how many were given to each name that day.

    I am trying to use a countifs formula. I have B:B as the Criteria_range1, and =COUNTIF(B:B,TODAY()) as Criteria1, but this doesn't work.

    Can somebody tell me what I need to enter to make this calculation work? Any help would be greatly appreciated.

    • Hi Douglas,

      The formula is correct and it worked perfectly on my test sheet. I can think of only reason for it not working - dates formatted as text. Is it the case?

      • Douglas says:

        I currently have column B formatted as Date, and chose the 03/14/01 format. :(

        • Douglas says:

          Thanks for the help Svetlana. I'm sure it is something minor I am overlooking, but I can't figure it out. I have tried formatting column B as text, date and custom but still cannot get it to work.

  150. Monica says:

    Please help me. I'm stuck on the following excel formula:

    =IF($H$26="Acting","","",IF($H$26="Temporary","","",IF($H$26="PERMANENT"(VLOOKUP($D$20,'Job Title'!$A$2:$C$28,3,FALSE),""))

    “Acting”, “Temporary”, or “Permanent” are text
    If H26 is “Acting” or “Temporary” return should be blank
    If H26 is “Permanent” it should perform a VLookUP at different D20 and compare it to spreadsheet named Job Title.

    Getting error: "The formula you typed contains an error"

  151. VINOD NAIR says:

    Hey, i need your help for

    Col 1 has Unique id ( Emp Code) and col 5 has date.

    i need to find out whether a Emp Code has Visited for more then once in a days.

    exp emp code 101 and Emp has Visited for more then twice a days in 2 place.

    need to make report regarding Emp Visited on tht day

  152. Grace says:

    Hello;
    I'm looking to set up a formula that will count all values in column A, when column B has a date of 2012?
    Thank you!

  153. Paul says:

    I've got a table of data that I have then created a number of concatenated columns from. From example, in my data, I have lists of contract numbers, and the month in which those contracts were processed (in some cases, this is across more than one month). I have therefore added a concatenated column that combines the contract number and the month number (1 for January, etc).

    I want to be able to find out the number of times each contract has occurred within each month so I have tried a simple countif formula that says =COUNTIF(A:A,C1). Column A contains the concatenated column and C1 is where I have the contract number and month number also concatenated for the countif to be based on. However, I get a zero every time even though I know that combination of contract and month number appear three times.

    Is my problem because all parts of the formula are referring to concatanated fields?

    Thanks for any help provided - it should be so simple but there must be some sort of limitation in Excel that is holding me back.

  154. Faith says:

    Hi Svetlana,

    I have a table that looks something like this:

    high run
    run
    run
    high run
    run
    run
    run
    run
    run
    high run

    I want to count the number of "run"s that occurs in the cell +1 column and -1 row of the cell with the value "high". So in this example, the return value would be 2.

    I am only able to count for one instance rather than the whole column. Can you please give any insights on writing the formula for this problem?

    Thank you

  155. Faith says:

    Hi Svetlana,

    I have a table that looks something like this: (0 is blank)

    A1 B1
    high run
    0 run
    0 run
    high run
    0 run
    0 run
    0 run
    0 run
    0 run
    high run

    I want to count the number of "run"s that occurs in the cell +1 column and -1 row of the cell with the value "high". So in this example, the return value would be 2.

    I am only able to count for one instance rather than the whole column. Can you please give any insights on writing the formula for this problem?

    Thank you

  156. Doulat Money says:

    Hi need to calculate how many times for particular month my in time is 9:30, 9:40, 8:20,10:20,9:50,..........in month end 9:50. Please suggest formula for how
    Many days I have come late my office time is 9:00.

  157. Martin says:

    Nice article, thanks!
    I have two columns of data like below. I need to count the number of times the value in column A is greater than the one in column B. Is this possible using COUNTIF?

    A | B
    2 | 1
    1 | 2
    3 | 0
    2 | 1

    Result A > B: 3 times
    Result B > A: 1 time

  158. Peter Fisher says:

    =COUNTIF(I:I,""&"")
    Column I contains formulas that return "", or a message, the message may vary. I am trying to count the messages, but the count returned is of cells containing a formula.

    e.g. the formula ="" put in a cell will increment the count

    This is in Excel 2013

  159. Silvester says:

    Hello please am trying to play around to see if i can use COUNIF or COUNTIFS to make my formula to count P and PS as one and the same thing in my totals. Thank you so much for the rapid response

  160. Hadi says:

    hey there, i wanted to sum or count a list, excluding some specific names. Like there is a fruit list and wanted to count/sum excluding apples and mangoes.

  161. Lidiya says:

    Hi Svetlana, It does work, you're right. However, in my real life example, I am using numbers instead of words, and it is not working.
    12.33
    12.33WR1
    12.35

    Count if should return 2, as I want to count all "12.33"&"*"

  162. Hi Lidiya,

    Oh, this is a problem. The only idea I can come up with at the moment is add up 2 functions, like this:

    =COUNTIF(range, "12.33") + COUNTIF(range, "12.33*")

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!
Ultimate Suite for Excel Professionals
 
 
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
Awesome!!!
Sheila Blanchard