COUNTIF function 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 365, 2021, 2019, 2016, 2013, 2010 and 2007.

COUNTIF function in Excel - syntax and usage

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

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

The syntax of the COUNTIF function is very simple:

COUNTIF(range, criteria)

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

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

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

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

Excel COUNTIF function examples

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

COUNTIF formula for text and numbers (exact match)

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

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

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

Similarly, COUNTIF formulas work for numbers. As shown in the screenshot below, the below formula perfectly counts cells with quantity 5 in Column D:

=COUNTIF(D2:D9, 5)
Excel COUNTIF formula for numbers.

In this article, you will find a few more formulas to count cells that contain any text, specific characters or only filtered cells.

COUNTIF formulas with wildcard characters (partial match)

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

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

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

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

Count cells beginning or ending with certain characters

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

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

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

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

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

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

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

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

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

Excel COUNTIF for blank and non-blank cells

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

COUNTIF not blank

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

=COUNTIF(A1:A10,"*")

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

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

COUNTIF(range,"<>")

Or

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 and with the "" criteria to count all empty cells.

Formula to count cells not containing any text:

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

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

Universal COUNTIF formula for blanks (all value types):

COUNTIF(range,"")

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

=COUNTIF(C2:C11,"")

Please be aware that Microsoft Excel has another function for counting blank cells, COUNTBLANK. For instance, the following formulas will produce exactly the same results as the COUNTIF formulas you see in the screenshot above:

Count blanks:

=COUNTBLANK(C2:C11)

Count non-blanks:

=ROWS(C2:C11)*COLUMNS(C2:C11)-COUNTBLANK(C2:C11)

Also, please keep in mind that both COUNTIF and COUNTBLANK count cells with empty strings that only look blank. If you do not want to treat such cells as blanks, use "=" for criteria. For example:

=COUNTIF(C2:C11,"=")

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

COUNTIF greater than, less than or equal to

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

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

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

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

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

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

Using Excel COUNTIF function with dates

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

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

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

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

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

Excel COUNTIF with multiple criteria

In fact, Excel COUNTIF function is not exactly designed to count cells with multiple criteria. In most cases, you'd use its plural counterpart, the COUNTIFS function to count cells that match two or more criteria (AND logic). However, some tasks can be solved by combining two or more COUNTIF functions in one formula.

Count values between two 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.

How this formula works:
Here, we use two separate COUNTIF functions - the first one finds out how many values are greater than 5 and the other one gets a count of values greater than or equal to 15. Then, you subtract the latter from the former and get the desired result.

Count cells with multiple OR criteria

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

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

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

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

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

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

Using COUNTIF function to find duplicates and unique values

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

Example 1. Find and count duplicates in 1 column

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

Example 2. Count duplicates between two columns

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

=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: It is probably the trickiest part of the COUNTIF function, which I personally find very confusing. Though if you give it some thought, you'll see the reasoning behind it - an ampersand and quotes are needed to construct a text string for the argument. So, you can adhere to these rules:

If you use a number or a cell reference in the exact match criteria, you need neither ampersand nor quotes. For example:

=COUNTIF(A1:A10,10)
or
=COUNTIF(A1:A10,C1)

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

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

In case your criteria is an expression with a cell reference or another Excel function, you have to use the quotes ("") to start a text string and ampersand (&) to concatenate and finish the string off. For example:

=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. both of the below formulas work equally well.

=COUNTIF(C2:C8,"<=5")
and
=COUNTIF(C2:C8,"<="&5)

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 and sum Excel cells by fill and font color.

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!

1026 comments

  1. Good morning,
    I have a sheet and I am trying to count if multiple criteria are met. First criteria is has to match a specific word and second criteria is has to be including and after date 1/1/24 which is contained in a different column. Here is what I have but it is not working and I have changed the sign between the two sets and no matter what I change it to it does not come out correct. Please if you could tell me what I need to fix.
    =COUNTIF(D900:D1077,"Task")+COUNTIF(E900:E1077,">=1/1/24")

      • Thank you very much for your help.

      • If I may, how can I take it a step further. I want to use this function and if these criteria are met then total up the dollar amounts that would correspond to the counted items in column O

        =COUNTIFS($D2:D1077,"Task",$E2:E1077,">=1/1/23")-COUNTIFS($D2:D1077,"Task",$E2:E1077,">=12/31/23")

  2. hi, good day to you. I am looking at the daily closing prices of a stock and would like to find out how many days (say) within a 20-day period that the price closed higher versus the day before. For instance, column A2:A21 are the dates and B2:B21 are the closing prices. Say if the price dropped every single day (vs. the day-before) except for One day where the price ticked higher (and subsequently continued to drop again), the Count would be ONE (1). That is, only One episode over the 20-day period, the price actually 'ticked higher'.
    Could kindly guide me please how best I could approach this problem?
    Thanks very much and wishing you & your loved ones bliss & health.
    Sincerely.

  3. I need to determine time in position based off <6 Months, <1 year, <2 years, 3 years. Can I use a countif for that?

    Jeremy 2/20/2012
    Chris 7/11/2022
    Lucas 4/26/2010
    John 7/8/2020
    Tyler 11/5/2018
    Daniel 4/17/2023
    Jeremy 4/29/2019
    Johnny 9/9/2019
    Josh 7/10/2017
    David 7/18/2022

    • Hi! Read the section above carefully: Using Excel COUNTIF function with dates. For example, this counts dates more than 3 years from the current date:

      =COUNTIF(B1:B10,"<"&(TODAY()+365*3))

  4. cell | value | formula
    A1 | |
    A2 | | =A1
    A3 | | =A2
    A4 | 0 | =COUNTIF(A1:A3, "")

    why value is not 2 what should i do if i want to output 2

    • Hi! Did you read the article above before asking the question? Pay attention to the following paragraph of the article above: Excel COUNTIF for blank and non-blank cells. For example, COUNTIF(A1:A3, "<>"&"")

      • conditional formatting rules

        apply to range | =A1:A3

        custom formula is | =AND(ISBLANK(A1), ISFORMULA(A1))

        highlight A2 and A3

        cell is blank but has a formula

        need how to count including formulas

        i beg you

  5. I am getting an unexpected result when using this formula. Any suggestions? I have two columns, one with a CY date data has been received and one with a PY date info was received. I am trying to count all of the rows where the CY date is blank, but we are past the PY date this year.

    =COUNTIFS('Sheet1'!F7:F75,"",'Sheet1'!G7:G75,"<"&TODAY()-"365")

    =COUNTIFS('Sheet1'!F:F,"",'Sheet1'!G:G,"<"&TODAY()-365)

    I have tried a few different ways now. I am coming up with 2 as an output when the data supports 7.

  6. =COUNTIFS(J4:J471,""&B492,J4:J471,"<="&EOMONTH(B492,0))

    The count is great but unable to count if in the same date.

    Eg :
    1/1/24
    2/1/24
    5/1/24
    5/1/24

    Total Sales in January : 3 (should be 4)

    • Hi! Your formula can only count values that are equal to cell B492. If you have dates written in column J, you compare those dates to the text. The second condition does not affect the result.

  7. Hi, in my mind this should not be a difficult yet I am struggling. I have a worksheet with a clients and dates. I want know how many different clients visited each month. Date is column A - client is column 7 and worksheet has 1,000+ rows.

    Date Name
    03-01-24 Jim
    05-01-24 David
    10-01-24 Jim
    15-01-24 Steve
    17-01-24 Steve
    25-01-24 Jim
    31-01-24 Alex
    01-02-24 Trish F
    05-02-24 Barb
    15-02-24 Trish D
    21-02-24 Lisa
    25-02-24 Jim
    28-02-24 Lisa

    Result for January: Jim (3) David (1) Steve (2) Alex (1)
    Result for February: Trish F (1) Barb (1) Trish D (1) Lisa (2) Jim (1)

    Please help! Thank you.

    • Hi! To get a list of unique values by criteria, use this instruction: Get a list of unique values based on criteria.
      For example:

      =UNIQUE(FILTER(B2:B14,MONTH(A2:A14)=1))

      If this formula is written in cell D1, to count how many times each unique name is written in a month, write this SUMPRODUCT formula in E1 and then copy it down along the column:

      =SUMPRODUCT((MONTH($A$2:$A$14)=1)*($B$2:$B$14=D1))

      You can change the month number for the MONTH function to get results for another month.

      • Thank you! That is great.

        How would I now create a summary - month by month? For example:

        Same data per above.

        Date Name
        03-01-24 Jim
        05-01-24 David
        10-01-24 Jim
        15-01-24 Steve
        17-01-24 Steve
        25-01-24 Jim
        31-01-24 Alex
        01-02-24 Trish F
        05-02-24 Barb
        15-02-24 Trish D
        21-02-24 Lisa
        25-02-24 Jim
        28-02-24 Lisa

        Summary:
        January: 4 total clients (this takes into account that Jim visited 3x and Steve visited 2x)
        February: 5 total clients (this takes into account that Lisa visited 2x)
        March:
        April:
        etc

        If possible, I would like to have a single formula in cell next January, February, etc.

  8. Hi Alexander,
    -First of all thanks for your article, I've using myself this Countif Function for a while and I believe it's super useful!
    -Secondly, I have tried to recreate the situation below (I hope the formatting is ok, otherwise, i have created some screenshots which I could send to you, which will probably explain better my Problem, but I cannot add the on this Forum, but I will try to summarize my problem below--> i have added on the "table" below on the Top, the Column Names and on the Right side, the Row Numbers).

    -Problem Description:
    ->I have the Following Sections on the table:
    --> 1. Tasks and Allocations of Group of People (aa, bb, cc) and People (o, p, q, r, s, t, u, v)
    --> 2. Total tasks per weeks
    --> 3. Group of People (aa, bb, cc) and People (o, p, q, r, s, t, u, v)
    -> The Inputs from B2:F12, I input manually
    ->By using the following Formula, I can count how many tasks are planned weekly (Results on Row 13: =SUBTOTAL(109;B2:B12) ) --> This works perfectly
    ->By using the following Formula on the array B14:F24 (=COUNTIF(B1:B12;"*"&INDIRECT(ADDRESS(ROW();1))&"*") ), I can know how many times the Groups of People (aa, bb, cc) and the people (o, p, q, r, s, t, u, v) have been allocated each week --> This is working fine as well and these numbers are the results you see in between B14:F24
    ->Now here comes my "Problem" where I am unfortunately going nowhere, is there a way that I can multiply the results of B14:F24 by the values of the tasks, on the weeks where the group of People or People have been allocated? So a Concrete Example: D17 has currently a value of 2, because through the function (=COUNTIF(D1:D12;"*"&INDIRECT(ADDRESS(ROW();1))&"*") ), it found on array D1:D12 the value "bb" twice. What I'd actually want, would be that everytime the value "bb" is found, it multiplies it by the value given on the Tasks Row (Task a, Task b, Task c), so in this case, to multiply it by D2 (Value=0,5, which is the weight Value of "Task a" on that week) and by D5 (Value=1, which is the values of "Task b" on that week). This will give me in return on D17 an added Value of 1,5 instead of 2.

    I hope my explanation is understandable, please let me know if you need any more info from me. I'd really appreciate you support. Thanks in advance.

    Regards,

    Vini

    Example:

    A B C D E F

    CW1 CW2 CW3 CW4 CW5 1
    Task a 1 1 0,5 0,3 1 2
    aa / o aa / o bb / q aa / o 3
    aa / p aa / p aa / p 4
    Task b 1 1 1 5
    bb / q bb / q 6
    bb / r bb / r 7
    Task c 1 1 1 1 1 8
    cc / s cc / s 9
    cc / t 10
    cc / u 11
    cc / v cc / v 12
    3 2 2,5 2,3 2 13 ---> Tasks sum
    aa 1 1 1 1 2 14
    o 1 1 0 0 1 15
    p 0 0 1 1 1 16
    bb 2 0 2 1 0 17
    q 1 0 2 0 0 18
    r 1 0 0 1 0 19
    cc 1 2 1 1 1 20
    s 1 1 0 0 0 21
    t 0 1 0 0 0 22
    u 0 0 1 0 0 23
    v 0 0 0 1 1 24

    • Hi! I don't really understand how you get a result of 1.5 in your example.
      I can suggest an alternative to your formula in D17:

      =SUMPRODUCT(--ISNUMBER(SEARCH(INDIRECT(ADDRESS(ROW(),1)),D1:D12)))

      This formula creates an array of 0 and 1, where 1 corresponds to the cell where the match is found. For more information, please read: How to find substring in Excel.
      You can multiply each value of this array by a specific value, such as D2:

      =SUMPRODUCT(--ISNUMBER(SEARCH(INDIRECT(ADDRESS(ROW(),1)),D1:D12))*$D$2)

      I hope this will help.

      • Hi,
        Thanks for your super quick reply, I really appreciate it. it is a bit difficult to expain it, because the formatting of the Table I put on my previous comment changed once I posted it :(. Any chance I can send you an Email somewhere so I send you my Screenshots and maybe you have some ideas about it?

        If not, I will try to explain as best as I can, I wouldn't like to use fixed values (Like $D$2),because I have a very large table, with around 1000 rows and like good 500 Columns on the Manual input Areas, so I cannot really fix the values, because otherwise I cannot populate properly the "Countif" Function, because I would be pulating always the same fixed value, which would not help me.

        I came up with an idea though, the Function that I have, works great already as a counter and I believe I could keep using it. =COUNTIF(B1:B7;"*"&INDIRECT(ADDRESS(ROW();1))&"*") ----> Is there any way I can use CountIf or any similar function as to get partial information inside of the cells where countif counted? --> I'd like to sum the values (last 3 digits of each cell from right to left) everytime for example "x" is found and then multiply this summed value of all cells where x was found by the counter I have already setup (This one: --> =COUNTIF(B1:B7;"*"&INDIRECT(ADDRESS(ROW();1))&"*") ) --> I prepared a new example below. Let's hope this time the formatting works...

        A B
        - a
        - aa / x / 0,1
        - aa / y / 0,1
        - bb / z / 0,1
        - b
        - aa / x / 0,3
        - aa / y / 0,1

        aa -
        x =COUNTIF(B1:B7;"*"&INDIRECT(ADDRESS(ROW();1))&"*") multiplied by the summed values of all cells after "/", everytime x was found
        y -
        bb -
        z -

        I really appreciate your support. Thanks a lot!

        Regards,

        Vini

        • Btw, "-" in my example above, are empty cells, just to give it more of a table character...

        • Hi! I don't really understand what result you wanted to get from your data.
          This formula sums all numbers in the cell range that are written after "x".

          =SUMPRODUCT(IFERROR(MID(A1:A10,SEARCH("x",A1)+3,10) * ISNUMBER(SEARCH("x",A1:A10)),0))

          • Hi Alex,

            Wow this is great!!!

            It worked almost perfectly with just one little hiccup.

            The function you provided will only sum the values, if the value that needs to be found "x" is available first on A1, because of the following criteria on your formula (SEARCH("x",A1). If the value is only available after, for example A2, then it unfortunately does not sum all the values after "x" on the range I have given and it returns a 0 :( ... Is there a way to change this? Because I would never know if the value is first Available on A1, A2, A3, .... or any other cell, within the Range I am looking at.

            Once again, a thousand thanks for your support!

            Regards,

            Vini

            • Sorry, I thought about something after writing my comment...

              Like, is there a way to get the Cell Number, where you first find the value you are looking for within a Range? In this way, I could replace this new Formula by A1... Just as an idea

              Once again, Thanks.

            • Hi! As I said, I tried to guess what you wanted to do. Unfortunately, you have not explained to me what kind of result you want. Your comments are hard to understand.

              • Hi Alexander,

                Sorry, the request is a bit hard for me to explain without any pictures, but I will try to do my best now. It might be a bit long, but I hope that you can understand me.

                What I am trying to do, is:
                1. I Have basically 2 tables (Table 1 and Table 2), one above, one below.

                2. The table above (Table 1) is where I will add all the manual Inputs.
                2.a. The table has as columns Calendar Weeks from roughly Column E - FG, and Multiple Projects on the Rows (Projects A - Project Z as example)
                2.b. So This table is the database where I am going to be inputting information and from where I am going to be extracting Information for the next Table (Table 2).
                2.c. For understanding what I am doing with this table, I am planning the capacity of colleagues per week for the next 2 years in multidisciplinary projects. So my intention is basically to manually input on each cell (when needed, if not needed the cells will be empty), the allocation of my colleagues. In this way, every project Manager of each project, plans its resources weekly for the next 2 years.
                2.d. When the project Manager of any Project plans its resources on each cell, I want them to add on each cell the following information: "Group of persons" & " " & "/" & "Person" & " " & "/" & "Capacity of the person per week (Values from 0,0 to 1,0, as to reflect the percentage that this person will be allocated to this task in this week"

                3. Now, about the second Table below (Table 2), this will be the table for my capacity planning which will take information from the Table above (Table 1). How it actually Works:
                3.1. On Column A, i have added "groups of person", this will be different company teams or subsuppliers that will have specific workers working for each Group --> I have roughly 10 "Groups of persons". These amount might change in the future
                3.2. On Row B, I have added "persons" which are basically direct under each "Group of person" where they belong to. --> I have initially created roughly 10 persons per group (as starting point)
                3.3. Now, the main result I am after and what I want the table below to do, is if you are on any cell on this table below (Table 2), to basically read the values on the Column "A" or Column "B" on the same row from the cell where you are at, which are going to be either the "Groups of Person" or the "Persons" and count how many times they have been allocated across the multiple projects on the table above on that specific week and with which capacity they have been allocated on each Project and add them together (this will be basically the values in between 0,0 - 1,0, mentioned on 2.d, which will be added after the second "/" at the end of the entry on each cell of the table above). In this case if for Example, "Person Nr. 1" has been allocated with 0,3 on Project A, with 0,5 on Project B, with 0,0 on Project C, with 0,0 on Project D and with 1,0 on Project E, I will know that the total allocation of the Person is 1,8 on that specific week and this will be reflected on the cell where I am standing.
                3.4. As you have different rows for "Groups of Persons" and "Persons", and because the cells on Table 1 have been configured with this information (See 2.d), it will depend on the row you are on, if the counter will count the allocation (sum of values from 0,0 to 1,0) of "Groups of persons" or "Persons"
                3.5. What I do already have managed to do and how this whole thread started, is to do a very simple countif, where I basically count how many times the "Group of Persons" or "Person" have been allocated across the multiple Projects during a specific week. Hence, the Formulas:
                3.5.1. Example of Formula for Rows on Table 2 where I have "Group of Persons": =COUNTIF(N8:N679;"*"&INDIRECT(ADDRESS(ROW();1))&"*")
                3.5.2. Example of Formula for Rows on Table 2 where I have "Persons": =COUNTIF(N8:N679;"*"&INDIRECT(ADDRESS(ROW();2))&"*")

                As a Summary, the Task I am after is the one described in 3.3.

                I hope this helps and you can help me further with my very particular situation.

                ***As closing statement: As I tried to explain on my previous comment, the formula you came with kinda helped, but unfortunately you don't know where exactly on the whole array of each week (Example N8: N679), your value is going to be found (For Example: "Person 1"), and if you do not know this, and you cannot reference a cell where this value is, then you only receive in return a value of 0***

                Once again, thanks again for the support. I'd understand as well, if it is too much what I am asking for, and you need to stop this thread. But if you come with any idea, I will highly appreciate it! Thanks in advance!

                Regards,

                Vini

              • Sorry the Thread before was so long, that I just noticed a small mistake... On 3.2., I meant "on Column B" and not "on Row B". i hope it is clear now.

                Regards,

                Vini

              • Hi! You have given a very detailed explanation. However, in order to provide you with a formula, I need an example of the data in the column and the result you want from that data.

              • Well, here we go again :), let's hope my example helps. For better understanding, I am doing it with only 2 columns and the separator in between the Columns is "\" and the Symbol "-" means that the cell is empty; Symbol "-->" Used within a statement just to clarify any point on each specific cell:

                Column A \ Column B
                - \ CW1
                Project A \ -
                - \ Group 1 / Person 1 / 0,3
                - \ -
                Project B \ -
                - \ Group 1 / Person 1 / 0,3
                - \ Group 2 / Person 3 / 1,0
                Project C\ -
                - \ Group 3 / Person 5 / 0,7
                -\ -
                - \ -
                -\ -
                - \ -
                Group 1 \ This should be 0,6 --> Because the "Group 1" has been allocated 2 times this week on Projects A and B, and on each allocation they have a value of 0,3
                Person 1 \ This should be 0,6 --> Because the "Person 1" has been allocated 2 times this week on Projects A and B, and on each allocation they have a value of 0,3
                Person 2 \ This should be 0, as it has this Variable has not been allocated this week
                Group 2 \ This should be 1 --> Because the "Group 2" has been allocated 1 time this week on Project B, with an allocation with value of 1,0
                Person 3 \ This should be 1 --> Because the "Person 3" has been allocated 1 time this week on Project B, with an allocation with value of 1,0
                Person 4 \ This should be 0, as it has this Variable has not been allocated this week
                Group 3 \ This should be 0,7 --> Because the "Group 3" has been allocated 1 time this week on Project C, with an allocation with value of 0,7
                Person 5 \ This should be 0,7 --> Because the "Person 5" has been allocated 1 time this week on Project C, with an allocation with value of 0,7
                Person 6 \ This should be 0, as it has this Variable has not been allocated this week
                Person 7 \ This should be 0, as it has this Variable has not been allocated this week
                Person 8 \ This should be 0, as it has this Variable has not been allocated this week

                Let me know if it is now clear?
                Thanks.

              • Hi! If I understand your task correctly, try the following formula:

                =SUMPRODUCT(ISNUMBER(SEARCH("Group 1",B2:B15)) * IFERROR(VALUE(RIGHT(B2:B15,LEN(B2:B15) - SEARCH("#",SUBSTITUTE(B2:B15,"/","#",2))-1)),0))

              • Hi Alexander,

                I want to say thank you for your patience and the effort you put onto coming with this Formula. It is exactly what I was looking for. So thanks a lot!!!

                As, I mentioned on my previous comments, I wanted to have keep the value to be searched, as variable (in your example "Group 1"), so I did a little twitch to your Formula and it works perfectly! In case it is interesting for you, the new formula looks like this: =SUMPRODUCT(ISNUMBER(SEARCH("*"&INDIRECT(ADDRESS(ROW();1))&"*";B1:B14))*IFERROR(VALUE(RIGHT(B1:B14;LEN(B1:B14)-SEARCH("#";SUBSTITUTE(B1:B14;"/";"#";2))-1));0))

                Once again, thanks a lot, it was a great exchange, have a great week.

                Regards,

                Vini

  9. Great article thank you

  10. I wanted to count the cell used displayed in pivot by headcount, whereas the (Headcount is 15) while (Headcount 2) is 4. See example below

    Row Labels
    (+/-) Headcount
    1
    2
    3
    ...
    15
    (+/-) Headcount 2
    1
    2
    ...
    4

  11. Hello,

    My issue is creating a count function for 3 criteria. My workbook has 12 columns of information and I am trying to create counting functions meeting filter options. For example, if I want to see how many individuals I have with a specific class license (column a) [what I am looking for] from a specific department (column b) [filter option], and years of service greater than 5 years [filter option], how would I construct that function?

  12. Hi.

    I'm looking to make a formula that the result would show whether or not a person (inspector) with a particular qualification conducted an inspection within a date range.

    For this scenario, we would have a column for the inspection date and a column for the qualification of that inspector as shown below.

    Inspection Date| Inspector Qualification
    01/12/24 | Insp.
    01/19/24 | QSP
    01/25/24 | QSD
    etc...

    The result would show a "0" or "1" (or preferably "Yes" or "No") if a QSD had conducted an inspection from January through March (between 01/01/2024 & 03/31/2024) of the current year. Preferably this would reset when 2025 comes around, but if not I can obviously change the dates of the formula.

    And...

    Same columns as above, another formula that would show whether or not a QSP conducted a inspection within the current month.

    The result would show a "0" or "1" (or preferably "Yes" or "No") if the QSP conducted an inspection within Jan, and then the result would reset in February.

    • Hi! To determine how many records in the table match the criteria, you can use the SUMPRODUCT function. In the formula below, you can use a reference to a cell with a date instead of the DATE function.

      =IF(SUMPRODUCT((A1:A10>=DATE(2024,1,1))*(A1:A10<=DATE(2024,3,31))*(B1:B10="QSR"))>0,"Yes","No")

      You can also use the COUNTIFS function. You can also find useful information in the following paragraph of the article above: COUNTIF formulas for dates.

      =IF(COUNTIFS(A1:A10,">="&DATE(2024,1,1),A1:A10,"<="&DATE(2024,3,31),B1:B10,"QSR")>0,"Yes","No")

  13. I have a spreadsheet with a column of number of units per order and a column of dates. There are multiple entries per date. I want to sum how many units were produced on each date.

    1 06/01/23
    1 06/01/23
    4 06/01/23
    1 06/01/23
    1 06/02/23
    3 06/02/23
    4 06/03/23
    1 06/04/23
    5 06/04/23
    2 06/05/23
    Continues. How do I get an output that summaries similar to:
    06/01/23 = 6
    06/02/23 = 4
    06/03/23 = 4
    06/04/23 = 6
    06/05/23 = 2

    • Additionally, I would like to use the master data tab as the source and have the output on a separate tab.

    • Hi! Pay attention to the following paragraph of the article above: Using Excel COUNTIF function with dates. It covers your case completely.

  14. I am trying to make a spreadsheet that counts the qty of parts delivered in a master sheet based on what part is delivered. I have over 300 parts that need counted. How can you easily create all 300+ criteria without having to manually input each criteria?

    • Hi! Your question is not entirely clear. However, the COUNTIF or SUMPRODUCT formula cannot calculate the number of parts for all items at once and provide you with a list. You can get a list of all parts by using the UNIQUE function. In the adjacent column for each item you can calculate the quantity as described in the article above. For a more precise answer, please describe the problem in more detail so that I can understand what you want to do.

  15. I just want to say how much I love ablebits and think yours is the best of all excel instructions available online.

  16. Have been trying to figure out how to get a sum of unique numbers (Column C) from the following example:

    Column C
    List of over 2000 Phone Numbers

    Column D
    Dates (example rows 2-52 are one date, rows 53 - 105 a different date and so on)

    Column F
    Date lookup value.

    What I'm trying to figure out is the unique sum of numbers in column C with a date in column D that matches the date in Column F Row 2.

  17. I have a question.

    A2:A15 contain some dates. I want to count how many dates in this range falls between each specific date range.
    C2:C9 contain a list of start dates
    D2:D9 contain a list of end dates

    This formula works (as you described above):
    =COUNTIF($A$2:$A$15,">"&C2)-COUNTIF($A$2:$A$15,">"&D2)

    But why does this formula not work? Considering that excel reads the datevalue?
    =COUNTIF($A$2:$A$15,AND(">"&C2,"<"&D2)

  18. I'm using the below to try to count the number of entries within November in a spreadsheet based on the date input into column A:
    =COUNTIF(Sheet1!A:A,">=01/11/2023"&"<=30/11/2023")

    There are 3 entries when i look manually but this produces the number 1 which is wrong. What is wrong with my formula?

  19. If i have with variouse name of differene employee and there check in time, how can count there late check in with bench mark of checkin time

  20. I am working on a spreadsheet, trying to figure out the number of clients sales. I have two columns with the year and number of times each client has ordered.

    I am a bit stumped on the formula. I would like to count how many people have ordered last year and this year. If Column B and Column C both have values and have a total of how many people have ordered both last year and this year. I have been playing with the formulas below but I can't seem to crack it. Are you able to provide any guidance ?

    • Hi! I don't have an example of your data, and I don't know what formulas you tried using. Based on your question, here is an example of formula:

      =COUNTIF(A1:A20,"2022")

  21. Is possible to count merged cells containing a specific criteria by using count if? For example, 5 cells merged into one containing the value "abcd". Bu using the COUNTIF formula, my result is 1 but I would like to have 5 because they are merged.
    Please let me know if there any way to do it, your help would be much appreciated.
    Best Regards,
    Daniel

    • I'm really sorry, looks like this is not possible with the standard Excel options. Also, if you split these cells, only one of them will contain a value.

  22. I work on a spread sheet where we make contact by dates with 3 different columns. First Attempt, Second Attempt, Final Attempt. I am trying to find a formula that will add up the total number of attempts by each date for each column. I have used =COUNTIFS(J2:J81,”>9000”) and I get the total attempts for that row but not the total for each individual date.
    Is that a possibility?
    Thank you kindly,
    Felicia

  23. How do I count the number of formula RESULTS that return a specified result.
    The formula is structured if-then-else =IF($A4="","",$B4)
    I want to count the number of cells that return the "else" value of B4 and not count the "" result if the first criterion is met.
    The actual formula is more complicated; thus I cannot simply count the non-blank values in col A

    • Hi! Your formula returns the value from column B for non-blank values from column A. I would suggest counting non-blank values using the COUNTIF function as described in the article above. I don't know why you don't want to do this. Without seeing your data, I can't recommend another alternative.

  24. Hi I need to count the number of dates that is in the past. However the dates to be counted is in every second column. A will be the test date, B will be the expiry date, C will be another test date and D the expiry date for C. Thus I need to count the dates expired in A, C, E, G ect. Help would be greatly appreciated.

    • Hi! The COUNTIF function count values by condition in only one column. Therefore, write a specific COUNTIF function for each column.
      If I understand your task correctly, try the following formula:

      =COUNTIF(B2:B10,"<"&TODAY()) + COUNTIF(D2:D10,"<"&TODAY())

  25. Your concern is appreciated,
    What does the "@" sign in the format cells mean?
    I know that "#" stands for a number
    I know that "$" stands for a Currency
    But also what sign or symbol is used for a text format?

  26. Hi, I'm looking for a formula with several conditions

    I have an excel master sheet where I need to create month on month graphs with over 2000 records
    Column A
    Reference numbers not all Unique
    Column B
    Creation Date (Contain Dates, no blanks)
    Column V
    Resolve date (contain dates and blanks)

    I have been trying to get the unique count based on below conditions

    2. First condition Count of anything that has a Creation date before 31/01/2023
    3. Then include data where Resolve date is after 31/01/2023 and all blanks
    4. Once i have that number I Need to have the count of all unique reference numbers from Column A

    Your help would be much appreciated
    Many Thanks

Post a comment



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