How to use Excel COUNTIFS and COUNTIF with multiple criteria

The tutorial explains how to use COUNTIFS and COUNTIF formulas with multiple criteria in Excel based on AND as well as OR logic. You will find a number of examples for different data types - numbers, dates, text, wildcard characters, non-blank cells and more.

Of all Excel functions, COUNTIFS and COUNTIF are probably most often mixed up because they look very much alike and both are purposed for counting cells based on the specified criteria.

The difference is that COUNTIF is designed for counting cells with a single condition in one range, whereas COUNTIFS can evaluate different criteria in the same or in different ranges. The aim of this tutorial is to demonstrate different approaches and help you choose the most efficient formula for each particular task.

Excel COUNTIFS function - syntax and usage

As already mentioned, the Excel COUNTIFS function is purposed for counting cells across multiple ranges based on one or several conditions. The COUNTIFS function is available in Excel 2016, 2013, Excel 2010, and Excel 2007, so you can use the below examples in any Excel version.


The syntax of the COUNTIFS function is as follows:

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
  • criteria_range1 - defines the first range to which the first condition (criteria1) shall be applied, required.
  • criteria1 - sets the condition in the form of a numbercell referencetext stringexpression or another Excel function, required. The criteria defines which cells shall be counted and can be expressed as 10, "<=32", A6, "sweets".
  • [criteria_range2, criteria2]… - these are additional ranges and their associated criteria, optional. You can specify up to 127 range/criteria pairs in your formulas.

In fact, you don't have to remember the syntax of the COUNTIF function by heart. Microsoft Excel will display the function's arguments as soon as you start typing; the argument you are entering at the moment is highlighted in bold.

The syntax of the Excel COUNTIFS function

Excel COUNTIFS - things to remember!

  1. You can use the COUNTIFS function in Excel to count cells in a single range with a single condition as well as in multiple ranges with multiple conditions. If the latter, only those cells that meet all of the specified conditions are counted.
  2. Each additional range must have the same number of rows and columns as the first range (criteria_range1 argument).
  3. Both contiguous and non-contiguous ranges are allowed.
  4. If the criteria is a reference to an empty cell, the COUNTIFS function treats it as a zero value (0).
  5. You can use the wildcard characters in criteria - asterisk (*) and question mark (?). See this example for full details.

How to use COUNTIFS and COUNTIF with multiple criteria in Excel

Below you will find a number of formula examples that demonstrate how to use the COUNTIFS and COUNTIF functions in Excel to evaluate multiple conditions.

How to count cells with multiple criteria (AND logic)

This scenario is the easiest one, since the COUNTIFS function in Excel is designed to count only those cells for which all of the specified conditions are TRUE. We call it the AND logic, because Excel's AND function works this way.

Formula 1. COUNTIFS formula with multiple criteria

Suppose you have a product list like shown in the screenshot below. You want to get a count of items that are in stock (value in column B is greater than 0) but have not been sold yet (value is column C is equal to 0).

The task can be accomplished by using this formula:

=COUNTIFS(B2:B7,">0", C2:C7,"=0")

And the count is 2 ("Cherries" and "Lemons"):

Counting cells with multiple criteria based on AND logic

Formula 2. COUNTIFS formula with identical criteria

When you want to count items with identical criteria, you still need to supply each criteria_range / criteria pair individually.

For example, here's the right formula to count items that have 0 both in column B and column C:

=COUNTIFS($B$2:$B$7,"=0", $C$2:$C$7,"=0")

This COUNTIFS formula returns 1 because only "Grapes" have "0" value in both columns.

COUNTIFS formula with identical criteria

Using a simpler formula with a single criteria_range like COUNTIFS(B2:C7,"=0") would yield a different result - the total count of cells in the range B2:C7 containing a zero (which is 4 in this example).

How to count cells with multiple criteria (OR logic)

As you have seen in the above examples, counting cells that meet all of the specified criteria is easy because the COUNTIFS function is designed to work this way.

But what if you want to count cells for which at least one of the specified conditions is TRUE, i.e. based on the OR logic? Overall, there are two ways to do this - by adding up several COUNTIF formulas or using a SUM COUNTIFS formula with an array constant.

Formula 1. Add up two or more COUNTIF or COUNITFS formulas

In the table below, supposing you want to count orders with the "Cancelled" and "Pending" status. To have it doen, you can simply write 2 regular Countif formulas and add up the results:

=COUNTIF($C$2:$C$11,"Cancelled") + COUNTIF($C$2:$C$11,"Pending")

Counting cells that meet any of the specified criteria

In case each of the functions is supposed to evaluate more than one condition, use COUNTIFS instead of COUNTIF. For example, to get the count of "Cancelled" and "Pending" orders for "Apples" use this formula:

=COUNTIFS($A$2:$A$11, "Apples", $C$2:$C$11,"Cancelled") + COUNTIFS($A$2:$A$11, "Apples", $C$2:$C$11,"Pending")

Another formula to count cells with multiple criteria and OR logic

Formula 2. SUM COUNTIFS with an array constant

In situations when you have to evaluate a lot of criteria, the above approach is not the best way to go because your formula would grow too big in size. To perform the same calculations in a more compact formula, list all of your criteria in an array constant, and supply that array to the criteria argument of the COUNTIFS function. To get the total count, embed COUNTIFS inside the SUM function, like this:


In our sample table, to count orders with the status "Cancelled" or "Pending" or "In transit", the formula would go as follows:

=SUM(COUNTIFS($C$2:$C$11, {"cancelled", "pending", "in transit"}))

A more compact formula to count cells with multiple criteria and OR logic

In a similar manner, you can count cells based on two or more criteria_range / criteria pairs. For instance, to get the number of "Apples" orders that are "Cancelled" or "Pending" or "In transit", use this formula:

=SUM(COUNTIFS($A$2:$A$11,"apples",$C$2:$C$11,{"cancelled","pending","in transit"}))

Counting cells with multiple criteria_range / criteria pairs and OR logic

How to count numbers between 2 specified numbers

By and large, COUNTIFS formulas for numbers fall into 2 categories - based on several conditions (explained in the above examples) and between the two values you specify. The latter can be accomplished in two ways - by using the COUNTIFS function or by subtracting one COUNTIF from another.

Formula 1. COUNTIFS to count cells between two numbers

To find out how many numbers between 5 and 10 (not including 5 and 10) are contained in cells C2 through C10, use this formula:

=COUNTIFS(C2:C10,">5", C2:C10,"<10")

A COUNTIFS formula to count numbers between X and Y

To include 5 and 10 in the count, use the "greater than or equal to" and "less than or equal to" operators:

=COUNTIFS(B2:B10,">=5", B2:B10,"<=10")

Formula 2. COUNTIF formulas to count numbers between X and Y

The same result can be achieved by subtracting one Countif formula from another. The first one counts how many numbers are greater than the lower bound value (5 in this example). The second formula returns the count of numbers that are greater than the upper bound value (10 in this case). The difference between the first and second number is the result you are looking for.

  • =COUNTIF(C2:C10,">5")-COUNTIF(C2:C10,">=10") - counts how many numbers greater than 5 and less than 10 are in the range C2:C10. This formula will return the same count as shown in the screenshot above.
  • =COUNTIF(C2:C10, ">=5")-COUNTIF(C2:C10, ">10") - the formula counts how many numbers between 5 and 10 are in the range C2:C10, including 5 and 10.

How to use cell references in COUNTIFS formulas

When using logical operators such as ">", "<", "<=" or ">=" together with cell references in your Excel COUNTIFS formulas, remember to enclose the operator in "double quotes" and
add an ampersand (&) before a cell reference to construct a text string.

In a sample dataset below, let's count "Apples" orders with amount greater than $200. With criteria_range1 in cells A2:A11 and criteria_range2 in B2:B11, you can use this formula:

=COUNTIFS($A$2:$A$11, "Apples", $B$2:$B$11, ">200")

Or, you can input your criteria values in certain cells, say F1 and F2, and reference those cells in your formula:

=COUNTIFS($A$2:$A$11, $F$1, $B$2:$B$11, ">"&$F$2)

Please notice the use of absolute cell references both in the criteria and criteria_range arguments, which prevents the formula from being broken when copied to other cells.

Using cell references in COUNTIFS formulas

For more information about the use of an ampersand in COUNTIF and COUNTIFS formulas, please see Excel COUNTIF - frequently asked questions.

How to use COUNTIFS with wildcard characters

In Excel COUNTIFS formulas, you can use the following wildcard characters:

  • Question mark (?) - matches any single character, use it to count cells starting and/or ending with certain characters.
  • Asterisk (*) - matches any sequence of characters, you use it to count cells containing a specified word or a character(s) as part of the cell's contents.
Tip. If you want to count cells with an actual question mark or asterisk, type a tilde (~) before an asterisk or question mark.

Now let's see how you can use a wildcard char in real-life COUNTIFS formulas in Excel. Suppose, you have a list of projects in column A. You wish to know how many projects are already assigned to someone, i.e. have any name in column B. And because we are learning how to use the COUNTIFS function with multiple criteria, let's add a second condition - the End Date in column D should also be set.

Here is the formula that works a treat:


Please note, you cannot use a wildcard character in the 2nd criteria because you have dates rather that text values in column D. That is why, you use the criteria that finds non-blank cells: "<>"&""

The COUNTIFS formula to count entries containing any text in one column and non-blank cells in another column.

COUNTIFS and COUNTIF with multiple criteria for dates

The COUNTIFS and COUNTIF formulas you use for dates are very much similar to the above formulas for numbers.

Example 1. Count dates in a specific date range

To count the dates that fall in a certain date range, you can also use either a COUNTIFS formula with two criteria or a combination of two COUNTIF functions.

For example, the following formulas count the number of dates in cells C2 through C10 that fall between 1-Jun-2014 and 7-Jun-2014, inclusive:

=COUNTIFS(C2:C9, ">=6/1/2014", C2:C9, "<=6/7/2014")

=COUNTIF(C2:C9, ">=6/1/2014") - COUNTIF(C2:C9, ">6/7/2014")

The COUNTIF formula with 2 conditions to count dates in a specific date range

Example 2. Count dates with multiple conditions

In the same manner, you can use a COUNTIFS formula to count the number of dates in different columns that meet 2 or more conditions. For instance, the below formula will find out how many products were purchased after the 20th of May and delivered after the 1st of June:

=COUNTIFS(C2:C9, ">5/1/2014", D2:D9, ">6/7/2014")

The COUNTIF formula to count dates with multiple conditions

Example 3. Count dates with multiple conditions based on the current date

You can use Excel's TODAY() function in combination with COUNTIF to count dates based on the current date.

For example, the following COUNTIF formula with two ranges and two criteria will tell you how many products have already been purchased but not delivered yet.

=COUNTIFS(C2:C9, "<"&TODAY(), D2:D9, ">"&TODAY())

The COUNTIF formula to count dates with multiple conditions based on the current date

This formula allows for many possible variations. For instance, you can tweak it to count how many products were purchased more than a week ago and are not delivered yet:

=COUNTIFS(C2:C9, "<="&TODAY()-7, D2:D9, ">"&TODAY())

This is how you count cells with multiple criteria in Excel. I hope you will find these examples helpful. Anyway, I thank you for reading and hope to see you on our blog next week!

You may also be interested in

894 Responses to "How to use Excel COUNTIFS and COUNTIF with multiple criteria"

  1. Michael says:

    Can COUNTIFS can be used to find the number of matches within the same range?

    I have a range of 500 postal codes in one column. These are formatted in two groups of three characters each (i.e. A9A 9A9). The first group of characters will change slightly to represent a different region - A9B, A9C, A9D, etc. I would like to get a count of each regional group.

    I could do this over and over again (as I've already done) as follows:
    =COUNTIF(M1:M500, "A9A*")

    This proved to be very repetitive to copy, paste and modify this 25 times for each of the regional postal code categories I'm working with. Can COUNTIFS do this at one time?

    • Hello Michael,

      Regrettably, COUNTIFS cannot help in your case because it counts cells that meet ALL of the criteria you specify in the formula. You can achieve the desired result in this way:

      Create an additional column and copy the following formula there =LEFT(A2,3) where A is your postal codes column. This formula will extrat the first 3 characters of the codes.

      Then you can proceed in 2 ways.

      Way 1:
      - Sort your table by the postal code column 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 region.

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

      Way 2:
      - 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 the original column with the postal codes to the Values section.

      You will get the same count of codes by region.

      • Ajay Arora says:

        if column A contains numeric value like = 1 2 3 4 5 6 7 8 9 10 etc...
        and column B contains three status like = Ontime, Late & Not Done

        then I want count-if of column A's numeric value 3 and 4 and column B contains status of Not Done

        kindly help me.

      • sainu says:

        sainu says:
        September 26, 2017 at 7:22 am
        Hi Svetlana,

        Could you please help to derive a formula for countif with 2 criteria


        cell range name is Date, i need a particular date plus
        another cell range is Method, under method there are 3 options say (SIF Entry, Excel Upload & Bulk), i need count if both criteria meets, my table for the function look like,
        SIF Entry Excel Upload Bulk
        03 sep 2017 Formula comes here Formula comes here Formula comes here
        04 sep 2017

    • BRAJESH says:


    • Jeethan says:

      I have the same problem where I want to count how many agents are working between 06:00 AM and 10:00 AM. How can i do that using countif function, send suggestions

  2. Michael says:

    Thank you. I just spoke to a colleague who walked me though a pivot table of my data. Thanks again.

  3. Emily says:


    I have a list of classes with the day and start & end times in one spreadsheet.

    The format looks like this:
    M 8:00 AM - 9:50 AM
    T 12:30 PM - 2:20 PM
    M 9:00 AM - 10:15 AM
    F 7:00 AM - 1:00 PM

    In another spreadsheet i have a table like this one below. I want to know how many classes occur on Mondays between 6:00 am and 6:29 am,6:30 am - 6:59 am,7:00 am - 7:29 am, etc.

    M T W R F
    6:00 AM
    6:30 AM
    7:00 AM
    7:30 AM
    8:00 AM
    8:30 AM
    9:00 AM
    9:30 AM
    10:00 AM
    10:30 AM
    11:00 AM
    11:30 AM
    12:00 PM
    12:30 PM
    1:00 PM
    1:30 PM
    2:00 PM
    2:30 PM
    3:00 PM
    3:30 PM
    4:00 PM
    4:30 PM
    5:00 PM
    5:30 PM
    6:00 PM
    6:30 PM
    7:00 PM
    7:30 PM
    8:00 PM
    8:30 PM
    9:00 PM
    9:30 PM
    10:00 PM
    10:30 PM
    11:00 PM
    11:30 PM
    12:00 AM

    I appreciate your help tremendously!

    Thank you!

  4. Emily says:

    oops, it's not pasting like i want it to. basically, each day is a column, each time is a row. sorry for the confusion.

    • Hi Emily,

      Can you please send me your sample workbook at support@ablebits.com and an example of the result you want to get? I believe in this way it will be easier for us to suggest a formula that returns exactly the result you are looking for.

    • Nitin Srivastava says:

      Hi Svetlana ,

      I 've send sample data Plz help me for get formula

      • Nitin Srivastava says:

        I want to how many HBL availbale in sheet against city
        ETAH HBL HBL

        • Nitin Srivastava says:

          I want to how many HBL availbale in sheet against city
          ALIGARH HBL HBL.
          ETAH HBL HBL
          ETAH HBL HBL
          ETAH HBL HBL

          cluster name HBL QTY REQUIRED

  5. New Excel user says:

    Trying to find a formula that will average only if EACH cell has a value greater than 0 in it; or if the last 3 consecutive months have a value greater than 0. Any help is greatly appreciated.

    • I believe, you can use a formula similar to this:

      =IF(AND(E2>0,F2>0,G2>0),AVERAGE(A2:E2),"-") where E, F and G are the last 3 months' values. You can add more > operators to the nested AND function if you want to check more than 3 months.

  6. rodz says:

    how can i automatically count or computed in Monday to Sunday data base count as week summary

  7. Glenn Campbell says:

    Column G is "Barcodes". Column J is "Carriers". How can it get the Excel to display "Flexsteel Truck or FedEx or UPS" in column J:J whenever a BX number (for example..BX09225)is enter in G:G?

    Thanks in advance,


    • Hi Glenn,

      If you want to use only formulas, then enter this one in cell J2:
      =IF(And(G2<>"", len(G2)>2,left(G2,2)="BX"),"Flexsteel Truck or FedEx or UPS", "")

      Then copy the formula across other cells in column J.

      If you want to insert some values in column J, but have these values replaced with "Flexsteel Truck or FedEx or UPS", or something like that, as soon as the code appears in column G, you need a special VBA macro to fulfill this.

      • Atul says:


        I have a query countiff not working. I have a project allocation sheet where column A is resources and B, C, D, E ....are daily dates. I have to allocate project to a resource in column A in other columns by selecting a drop down I have created in Column B onwards. Now I want a summary that how many resources are in a project end of month.

        Suppose 5 resources were allocated Bug fixing project through out the month the formula would now count resources for me it counts the allocations i.e. number of times I allocated bug fixing to resources

        Please help

  8. Kees Struik says:

    Help please.
    3 columns. Column A is a number (Day), Column B & C are time.
    Count rows where day number is less than 6 and times in either B or C are between 5:00 & 7:00

    A B C
    D T0001 T0002
    1 6:14
    1 6:57
    1 6:58
    1 20:50
    1 23:05
    2 17:47
    2 17:59
    3 6:01
    3 17:46
    5 18:20
    6 7:06
    6 11:50
    6 13:29
    6 13:53
    6 14:57
    6 17:36
    7 10:26
    7 10:32

  9. Kees Struik says:

    Column B & C data not pasted very well. Just put every second time in column c.

    • Hello Kees,

      I believe the following formula will work a treat:

      In your case, the result will be 4.

  10. Jason Pisani says:

    Hi Svetlana I sent you an email for your assistance could you be so kind to help me? thanks Jason

  11. gio bitoy says:

    Sex Age
    F 14
    F 19
    F 15
    F 14
    F 16
    F 21
    F 24

    how can i count with these limitations? what formula/syntax will i use?
    i need to count all F with age less than 15, F equal to 15 but not more than 19 and all the F equal 20 but not more than 24

    I want to tabulate it in this format.

    F less than 15
    F equal 15 pero not more than 19
    F equal 20 but not more than 24
    thanks for helping me on this one

    • Hi Gio,

      Try these formulas:

      F is less than 15:

      F equal 15 pero not more than 19:

      F equal 20 but not more than 24:

  12. Brad MacNamara says:

    I am trying to add a value for a Alpha value in a cell. The Cell can have an M=5, a C=3 or a Z=1 so in the same cell I want to show a value of the corresponding letter, with a default of "0" if they do not choose either letter.
    Also I am adding another value in a subsequent cell for "C" a value of 1,2,3 will be used as as a negative of 1=-0.5, 2=-1 and 3=-1.5, same values only apply to a Z 4=-.5, 5=-1, and 6=-1.5.

    Thanks for your help in advance...

    • Hi Brad,

      Sorry, I'm not sure I can follow you. If you can send me your sample workbook at support@ablebits.com, I will try to help.

      • Imam Azad says:

        Dear Svetlana Cheusheva,
        I appreciate your help tremendously!
        can you inform that,

        above there if i use the formula =COUNTIF($F$8:$F$30,"A") than result is 3
        but i wants to know that, How Would I Count Only The Same Name Once/ Count Repeated Items Once.
        Please help me urgently.

        Thanking you,
        Imam Azad
        Macro Cable Ltd
        Dhaka, Bangladesh

        • Hello Imam,

          If you want to get the list of unique values, you can copy your list to a new sheet and click on the Remove Duplicates icon under the DATA tab.
          If you want to get something different, please describe your task in more details.

          • Imam Azad says:

            Dear Svetlana Cheusheva,

            Please solve my example: Mr. Karim is market visited statement as below-

            Date Code Name Type of Customers

            10 October 2014 100 ABC Traders Traders
            11 October 2014 101 MNZ Traders Traders
            12 October 2014 201 AKA Builders Developers
            13 October 2014 300 PWD Consultant
            14 October 2014 100 ABC Traders Traders
            15 October 2014 300 PWD Consultant
            16 October 2014 201 AKA Builders Developers
            16 October 2014 103 ABD Traders Traders
            17 October 2014 100 ABC Traders Traders
            17 October 2014 101 MNZ Traders Traders
            19 October 2014 104 PQS Traders Traders
            20 October 2014 100 ABC Traders Traders

            Now I want to report/result:

            1. How many times visited there? Ans: 12 times. it's i can do.

            2. How many customers are in visited there?

            Please forward formula how to solve.

            Thanking you,
            Imam Azad
            Macro Cable Ltd
            Dhaka, Bangladesh

          • Imam+Azad says:

            Dear Svetlana Cheusheva,
            Please help any update.

            Thanking you

          • Imam+Azad says:

            ANY UPDATE
            Please be Reply


  13. vcoolio says:

    Hello Svetlana,

    I noticed that in your tutorial that COUNTIF formulas are used outside the columns/cells that need to be totalled.

    I have a work sheet with a number of columns, one of which is Sales/Purchases. The data in the work sheet is extensive and the columns are very long so I like to keep track of the number of Sales and Purchases for a month by using =COUNTIF(range,"SALE")and the same for Purchases. If I place the formulas in a cell at the top or bottom of this column everything works fine. However, should I place the formulas outside this column all I get is a date (such as January 1900). Am I doing something wrong or is the formula restricted to its specific column?

    Thank you in advance for any help.


    • Hello Vcoolio,

      You can put your formulas in any cells. For the formulas to work correctly, try the following:

      1 Apply the General format to the cell with the formula (press Ctrl + 1 and select General in the Format Cells dialog window).
      2 If you used relative cell references in the range argument, the references got distorted when you copied the formula. Try using the absolute cell references instead (e.g. $B:$B for column B).

      If the above suggestions do not work, please send me a sample workbook at support@ablebits.com and we will try to figure out the source of the problem.

    • Kanchan Khandekar says:

      I need a help to do below thing. Suppose I have column A which contains values either of A,B or C as Follows.

      Column A
      Now what I have to do is, I want to count pair of AA, AB, AC likewise, based on the two consecutive values, if First row contains A and 2nd row contains A, then AA count should be 1 likewise go on.
      What I tired is I can get count for two cells only using following formula =COUNTIFS(A1, "=A",A2,"=B")
      But I don't how to go on increasing the rows.
      Please help

  14. vcoolio says:

    Dear Svetlana,

    Thanks for that. I've sorted it out. Someone had been clearing cells by pressing the space bar and obviously a space is still a character so this was distorting any new entries such as formulas. I've made a note of your resolutions above for future reference. Excellent stuff!

    Your help is much appreciated (and is worth a vodka or two!!).


  15. Brandon says:

    Hi, I have a range of cells I want to count for a skills matrix for my staff. The ranges of what I want counted are drop down menus in each of the cells with "Sound", "Advanced", "Expert" etc, and these I wanted counted in the number of times that the cells have one of those words in them. The other cells contain "UnAware" and "Basic" which I dont want counted in the original total, but put in a total for all the types of details entered, this way I will get a % of staff with the desired level of training/competence in the processes they use every day.
    I tried COUNTIFS but I kept getting a zero count even though I used the formula correctly (no error message). Could you please help me work this out?

  16. Mr Sunny says:

    Hi Svetlana,
    Could you be kind enough to help me out on this excel problem I am trying to refer to a particular cell for formatting but there is a conflict. I will appreciate your prompt response.
    I have sent the file to your mail.

    • Hi!

      In your case, it probably makes sense to look up by 2 columns C and D using an array INDEX / MATCH formula. I've sent you an email with a formula example, hopefully that is the result you are trying to achieve.

  17. Angela says:

    Can I use countifs with more to count more than three ranges and criteria?

  18. Shad says:

    Hi Svetlana,

    How can I count two different texts in one column?

    So my usual formula is: =COUNTIFS(C:C,"Passed",D:D,"Quebec")
    And its giving my correct figures.

    But if in the column C, i also want to count any other status that says Remediation Passed, how should I say that? No change in column D. I tried something like, COUNTIFS(C:C,"Passed" OR "Remediation Passed",D:D,"Quebec"), but that didn't work.

    Grateful for your insights.


    • Alejandro says:

      =COUNTIFS(C:C,"Passed",D:D,"Quebec")+COUNTIF(C:C,"Remediation Passed)

      This is useful for differing values.



      This is more efficient and works to find anything containing the word "Passed" as in your example.

  19. Tim says:

    Hi Svetlana,

    I was trying to follow your tutorial and adapt it to my data, but it didn't seem to work. Would you mind helping me troubleshoot please? :)

    I was trying to create a COUNTIF for instances when there's text in column A AND text in column C OR column D.

    Thanks in advance!

  20. katy says:


    I have a workbook with mulitple sheets and an overview sheet.
    The overview has validation list for different months whicg relates to each of the muliple sheets.

    I need a count if where by the sheet name in the range will change depending on the month selected in the validation

    i.e If 'May-14' in cell 'Overview!'C2 - Countif('May-14'!R:R,Criteria)
    If then in overview cell2 it is changed to Jun-14 the Countif will change to Countif('Jun-14'!R:R,Criteria).

    Please help?!

  21. KC says:


    I have two columns named as Reportable (with Yes or No) and another column named Responsible_Officer.

    I am trying to do a number count for each yes per each individual responsible officer.

  22. Ansu says:

    Help needed on the formula for getting the desired count as shown in the table.
    Need to get count as 1 when either or both the parts are 100% completed.

    Name Part Status Desired Count
    A LHS 100.0% 1
    A RHS 100.0%

    B LHS 100.0% 1
    B RHS

    C LHS 1
    C RHS 100.0%

    D LHS 0
    D RHS

    Thanks a lot in advance.

  23. Mark says:

    I am trying to tally the number of calls by day-of-the-week each person has from a table that looks like this but for the entire year:

    JANUARY Sa Su Mo Tu We Th Fr
    James call
    John call call
    Mike call
    Jane call call

    FEBRUARY Sa Su Mo Tu We Th Fr
    James call call
    John call call
    Mike call
    Jane call call

    MARCH Sa Su Mo Tu We Th Fr
    James call
    John call call
    Mike call call
    Jane call

    I have a table at the top of the spreadsheet that shows the results and looks like this:

    Sa call Su call Mo call etc
    James ?? ?? ??
    John ?? ?? ??
    Mike ?? ?? ??
    Jane ?? ?? ??

    What formula do I use that references the names in the left column with the rows to give me the ?? tallies based on day of the week when they occur? Thanks.

  24. Pagz says:

    How do I use countif to meet the same criteria but across multiple ranges in multiple worksheets?

  25. Jerrie Mar says:

    Hi Svetlana,

    this is my problem

    A1 B1 C1
    6:00 - 14:00 14:00 - 20:00 20:00 - 6:00
    A2 = B2 = C2 =
    A3 = B3 = C3 =

    for example, i have a value in different excel sheet.
    if i enter 8:00 am there, it will be automatically appear in A2.
    B2 and C2 will be blank.
    but if i put the value 15:00 pm, it will be automatically appear in B2. A2 and C2 will be blank.

    Hope you can help me with this.. Thanks

  26. Jerrie+Mar says:


    A1 is 6:00 AM
    B1 is 14:00 PM

    A2 is 15:00 PM

    B2 =A2 (This should not appear in the range of A1-B1) it will leave blank

  27. Jix says:

    First Column 2nd column
    List =LST what is the formula of getting a shortcut e.g i type
    Cable List next column will automatic appear LST and so and so fort
    Wire Thanks awaiting your prompt response.

  28. Kolegu says:


    How can I count if criteria is in a list?
    For Example: instead of =COUNTIF(Sursa!$G:$G,"Answered")+COUNTIF(Sursa!$G:$G,"Blind Transfered")+COUNTIF(Sursa!$G:$G,"RONA")

    to have something as countif(Sursa!$G:$G,"Answered" or "Blind Transfered" or "RONA")
    Thank you.

  29. Kolegu says:

    I found the answer for: COUNTIF(Sursa!$G:$G,"Answered")+COUNTIF(Sursa!$G:$G,"Blind Transfered")+COUNTIF(Sursa!$G:$G,"RONA")

    instead of it, the formula is:
    SUM(COUNTIF(Sursa!$G:$G,{"Answered","Blind Transfered","RONA"}))

    for those who will have this problem in the future.

  30. Linda says:

    pls av been trying to formulate a formula for my work and I need ur help. am dealing with different index nos and there various transactions whether credit or debit. I need a formula that can help me count de individual no of credit and debit for each index no and also sum the various credit and debit amount.

  31. Jane says:


    I'm trying to count the number of times a specific reason code (27 codes in total) occurs within a specified date range. I was able to use the COUNTIFS statement to identify the date range, but I can't figure out how to add the second part of the equation to the formula - that of now counting how many times each of the 27 codes occurs within the date range. The formula I'm using is:
    =COUNTIFS($A$17:$A$50,">="&$R$30,$A$17:$A$50,"<"&$R$31). Any suggestions as to how I can add the next part of the statement?

    Thanks for your help!

    • Hello Jane,

      Just add one more Range/Criteria pair to your formula: =COUNTIFS($A$17:$A$50,">="&$R$30,$A$17:$A$50,"<"&$R$31, $B$17:$B$50,$R$32)
      Where $B$17:$B$50 – the cells that contain the reason code, and $R$32 is the cell with the code you want to count.

  32. avp says:

    Hi, I am having a country, site and subject number column and visit 1 to visit 62 columns with the date information within an excel spread sheet. I would like to calculate
    - count of subjects per country per site per visit within the specific date range
    - count of subjects per country per visit within the specific date range

    As I am having a huge amount of data I would prefer to avoid the manual work which will take a lot of time to find out this information. Can you please help me?

  33. angelo says:

    hi Mam,

    I sent you an email, hope you can help me with my problem.



  34. Raja says:

    hi Svetlana,
    now I'm working with Windows 7
    here are the data in A1:E6, now im using array to count them by each character:
    89 89 89 89 9
    35 35 35 35 47
    39 39 39 39 48
    356 356 356 356 46
    3589 3589 3589 3589 3459
    1 1 1 1 12

    and the reault will be:

    1 2
    5 6
    9 8
    14 9
    0 0
    17 3
    13 5
    4 4
    5 1
    1 7

    is it possibble to sort them from smallest to biggest instead in one formula? so the result will be..

    0 0
    1 2
    1 7
    4 4
    5 6
    5 1
    9 8
    13 5
    14 9
    17 3

    thank you for your help..

  35. Subbu says:

    In a coloumn, there are 4500 cells that contains values like -20000,-1000,100,200,500,1500 etc so i want to count there are how many 100s,200s,300s,-100s etc but if i use "COUNTIF" formula i have to give every time its very time taking.
    So plz tell me formula that gives the count there are how many 100s,200s etc are there

    • Hi Subbu,

      - Insert a pivot table (select your column and go to Insert > Tables > PivotTable).
      - Place the pivot table onto a new sheet.
      - Drag and drop your column from "Choose fields to add to report" to the Rows section
      - Drag and drop your column from "Choose fields to add to report" to the Values section.
      - Click on the column name in the Values section and select "Value field settings" from the drop-down menu.
      - Select "count" instead of "sum" and click OK.

      If you want to update information, right-click on the pivot table report and select "Refresh" from the context menu.

      • MICHAEL says:

        Name Gender Religion DIT0101 DIT0102 DIT0103 DIT0104 DIT0105 TDH 1100 total Average Grade Award
        Jane female Christian 56 88 88 99 65
        Maulid male Muslim 87 56 75 67 56 87
        Hanifer female Muslim 56 34 67 87 78 44
        hardeep female Hindu 67 36 43 34 35
        janet female Christian 45 89 87 4 67
        Andrew male Christian 45 68 88 23 87
        Habib female Muslim 67 76 32 87 56
        vijay male Hindu 75 56 87 28 45 78
        Jack male Christian 88 67 34 77 67 32

  36. Lindsey says:

    I'm creating a spread sheet to work with attendance numbers for an after school program. I'm using this formula for each grade.


    I want to copy the formula across a spread sheet so that it will calculate daily attendance for each class. To do this, I need to be able to get the row to flow like this:


    But instead, it copies like this:


    Is there a way for me to set this up so that only the DailyAtten! value progresses when I continue the formula?

    Thank you!

  37. Rich says:


    =COUNTIFS('Current Enlisted By Name'!$E:$E,LEFT(B6,4)&"*",'Current Enlisted By Name'!$D:$D,"WHQK"&"*")

    I need that formula above to capture these UICs only:


    Please help

    • Hello Rich,

      This can be done either with a helper column or VBA.

      If adding a helper column is acceptable, say column K and copy the following formula there, starting from row 2:

      Your main formula will be:
      =COUNTIFS('Current Enlisted By Name'!$E:$E,LEFT(B6,4)&"*",'Current Enlisted By Name'!$K:$K,TRUE)

  38. Jason says:

    I have tried using the same multiple if's with a table that has multiple data validation lists. The user simply picks under the source column where the lead came from and then in the type column they would pick what type it came in as.

    My formula is =countif(Table1[Source],"Kijiji",Table1[Type],"phone") to find out what percentage of leads were sourced from kijiji by phone as opposed to kijiji by email or our website by phone.

    Not sure what I am doing wrong because it keeps throwing it out saying it is causing arguments.
    Any help would be appreciated.

  39. Monique Drummond says:

    I'm working from a spreadsheet and pulling data from the spreadsheet into a table on a new worksheet. I need to countif the data from A2:A114 is greater than 12/31/2013 and less than 4/1/2014 to obtain a count for Q1. I need to do this for each Quarter and change the dates which is working fine.

    For example: =COUNTIF('Partnership Tracking '!$C$2:'Partnership Tracking '!$C$114,">12/31/2013")-COUNTIF('Partnership Tracking '!$C$2:'Partnership Tracking '!$C$114,">3/31/2014")

    Now I need to know of the data returned from the above formula which of those have Cleared using values in columns W2:w114 and the word "cleared". I tried this one and it is not yielding a realistic number.

    Getting error:
    =COUNTIF('Partnership Tracking '!$C$2:'Partnership Tracking '!$C$114,">12/31/2013")*COUNTIF('Partnership Tracking '!$C$2:'Partnership Tracking '!$C$114,"<4/1/2014")

    Is it possible for me to achieve what I need?



    • Hello Monique,

      If I understand the task right, the following formula will work a treat:
      =COUNTIFS('Partnership Tracking '!$C$2:'Partnership Tracking '!$C$114,">12/31/2013",'Partnership Tracking '!$C$2:'Partnership Tracking '!$C$114,">3/31/2014", 'Partnership Tracking '!$W$2:'Partnership Tracking '!$W$114,"cleared")

      If it doesn't, you can send us your sample workbook at support@ablebits.com and we will try to help.

  40. Lorna says:

    Svetlana and team – I enjoyed reading your blog about the “COUNTIFS” function, but am having problems using it. I have a worksheet that I want to count the number of unduplicated rows with two different criteria. I want to count the number of completed actions by analyst, but only count each class code once. The class code is in column “G”, the analyst (there are five different analysts) is in column “C”, and the analysis/recommendation is in column “AF”. When an analysis is completed for a particular class code, the column “AF” has some text. If the analysis is not complete, “AF” is empty.

    I can successfully count the number of unduplicated class codes in my worksheet, by using:
    My worksheet has 7,495 rows of duplicated class codes and 3.666 rows of unduplicated class codes.

    I can successfully count the number of analyses completed by analyst, by using:
    What I’m trying to do, but can’t figure out is how to use the COUNTIFS function and count the UNDUPLICATED number of analyses completed.
    Can you help?

    • Hello Lorna,

      You can use the following array formula, please don't forget to press Ctrl + Shift + Enter to complete it:

      But I'd recommend adding a helper column and copying the class codes there according to your conditions, e.g. here is the formula for row 2:
      =if(AF2<>"", G2, "")
      Then copy this formula down to the other cells in the helper column and count using your first formula. This seems to be a quicker and more flexible approach.

  41. Nancy says:

    I am trying to take 2 tables - Galaxy Theatre and Empire Theatre. Each table contains a list of the theatres and the gendre of movie playing in the theatre. I need to count the number of horror movies playing in each theatre (provide a total for each theatre) using one equation. Is this possible?

    The question suggests using an indrect to help, but I cannot get the Indirect function to work with countif.

  42. Freedom says:

    I need to track escalation in certain readings.
    For example, I have 2 Columns, A and B; I need to count how many times column B is greater than column A when column B is greater or equal to 10.

    Sample Data:

    A B
    5 8
    10 15
    14 11
    18 22
    9 10

    From above, the answer/count should be 3

    Your help is great appreciated

  43. Robman23 says:

    Hello Svetlana!

    Good day!

    Please help me, I'm a begginer on Excel. I have an exam today that may include below; (see data and scenario below)

    (Scenario: Need to count the total no of landline nos, I use =COUNTIFS(DATA!D5:D135,">1") formula however it only gives me 20 total counts, where there should be a total of 21 inlcuding 3401031 LOC. 213. What formula should I use then? Please :) )


    3401031 LOC. 213

  44. clogsden09 says:

    I am working with multiple workbooks, about 30. Each workbook has the names of associates that performed different tasks in it. I am trying to count how many times each persons name is used in all the workbooks.
    (Ex. Workbook 1 - Jane is used twice, Bill three times. Workbook 2 - Jane is used 4 times, Bill 1 time. Jane's total would 6 and Bill's would be 4 within two workbooks. Can the COUNTIF or COUNTIFS functions acomplish this in an easy way?

  45. Nina says:

    I'm trying to calculate how many times a particular staff is scheduled to work during the week. This staff can be assigned to work in a different location so she can be listed in a column (to specify the day of the week) and rows are the location. The only problem is that I would only be needing to add two to five cells in a column, then another few cells from another column. How would I need to indicate that I'm adding another command?

  46. Andrew says:


    I have a project involving several varying length lists of email addresses which are overlapping, meaning there are duplicate values between the various lists. Additionally, the list order from left to right is important. Each list is an adjacent column in a single worksheet.

    I would like to be able to identify (highlight via conditional formatting) the duplicate values in each successive list (column) occuring in the preceding list(s) to the left of column I'm evaluating, working my way to the nth column all the way to the right in my worksheet. For example, evaluating column B, highlight all cells where there is a duplicate value somewhere in column A. Evaluating column C, highlight all cells where there is a duplicate value anywhere in columns A or B. And so on up to my last column (20) which will be evaluated for matching values anywhere in the other 19 preceding columns to the left. Note that there are no duplicate values within any single list.

    Is there a formula I can use to achieve this goal? Thanks for any suggestions you can offer!


  47. Raja says:

    Hi ,

    By having the Alphabets (A-Z) & numbers consider from (0-9) need the combination of these only with 2 characters alone eg:
    Could you please give an excel formulae or as a vba macro code to display in an excel.
    Please drop me an email.

  48. Bernie says:


    I want the cell range in =COUNTIFS to be variable, ie. it might be b4:b60 on one occasion, then with a different set of data it might be b4:b70.
    Can the COUNTIFs formula pull the range in from another cell if the range is entered there?



  49. Leo says:

    Hi, is it possible to count B4:D200 (multiple column) and a column of uncategorized age to be counted in a column of categorized age?


  50. CARMEN says:

    I am trying to create a formula that will give the total number of cells within a range that contain a letter. The formula I currently have is: =COUNTIFS(C4:AC4,"=L",C4:AC4,"=F",C4:AC4,"=T"), however it is ot correct because within this range of cells three of them contain each a letter which are the ones in the formula and my total shows as 0 instead of 3.

    What should I change to get it corrected or would it be possible to even have such formula?

  51. lera says:


    I have an excel file with 2 worksheets: the first worksheet contains 10 columns and 10 rows with random values (A, B, C, D, E).

    I need a formula in the second worksheet to count the number of times that value "B" appears after value "A" in the first worksheet.

    It's really important and urgent, if you can answer this you would save me a big headache.

    Thank you

  52. Carlo Mantini says:


    I am counting up unique string values in a range of cells in a column. Tow of the values are "Uses Look Up" and "Value in table". To attempt to get a sum of the counts of the 2, I set up the COUNTIFS functions as follows:

    =COUNTIFS(F1:F128,"=Uses Look Up",F1:F128,"=Value in table")

    The value retuned is 0, however, when I count them separately the count is 75. Why did the COUNTIFS function return 0?

    Thanks, Carlo

  53. Ryan says:

    Is there a way to have a total price adjusted based on the day they typed a quantity into a cell? Lets say price goes up on oct 24 for apples, from $10 to $15. Is there a way to have it calculate the order of 2 apples on Oct 23 (before the 24th), but also calculate the total if they order the apples on the 25th (after the 24th)? If they didn't type in the quantity till after the advanced priced date. This has to be real time.

    Thanks, Ryan

  54. Muhammad shafiq says:

    Dear Mam ,
    I will appreciate to resolve my problem.
    i have different date in column which i required the count where dates is mentioned and rest blank not count .
    Please help me

    Center Revised Date 2nd PM Plan Date 3rd PM Plan Date Total Count

    Bannu-1 1-Oct-14 1-Oct-14 Blank ?(Answers should be 2)

    • Hello Muhammad,

      You can use this formula to count all non-blank cells:

      To count only dates and numbers greater than 0, please use the following array formula (you have to press Ctrl + Shift + Enter to complete it):


      Where A2:F2 is the range you want to count.

  55. Prasad says:


    I have a data in 2 rows 1st row contains date, and in the 2nd row i have comment codes, i want the latest comment code and latest date in a separate cell in the same row.


    1st Row: 1-oct, 2-oct, 3-oct, 4-oct, 5-oct, 6-oct, 7-oct,.....
    2nd Row: ASDF DSFA FGHJ WERT, SDFA .....

    I want the latest comment and date at the end of row for each row.

    I hope you understand the requirement...please help me.


  56. Utkan Ay says:

    Hi Swetlana,
    Hope you doing well.
    I am having difficulty with formulas of multiple critearia.

    My formulas below is worked with me.
    I want to add one more criteria which is date period.My Dates coloumn is starting from C1 till C1000.
    The target date is at B1(15oct2014)
    How can I continue to my formulas which will give me the total after 15Oct2014.
    If you can help me on it will be appreciate.

    Thanks & Regards.

    • Hi Utkan,

      All ranges in COUNTIFS formulas should have the same number of rows and columns. Most likely, you need this formula:

      =COUNTIFS(ACTL!B3:B50001;A$1;ACTL!$E$2:$E$50000;B$2;ACTL!$G$2:$G$50000;$B7, ACTL!$C$2:$C$50000;$B$1)

  57. Floris says:

    Problem I cannot seem solve.
    Looking for a formula that counts occurrences of a value IF they occur after a certain date.
    This works fine with SUMIF e.g. =SUMIF($O$2:$IU$2;"<=" & TODAY();O26:IU26) Where the sum of numbers after a certain date are returned.

    DATASET looks like:
    DATES : 25/10|26/10|27/10|28/10|29/10...
    TASK A done by: A | B | A | A | C
    TASK B done by: B | A | B | C | A

    I want to know how many times A, B and C occur after TODAY...

    Thanks a lot for your much appreciated help!


    • Hello Floris,

      Please specify how many tasks and how many A/B/C/ for each task you have. Are the dates sorted in ascending order? Do you want to get the result on the same sheet or on a new one?
      Anyway, I believe the best solution for this task is to use a VBA macro.

  58. Avi says:


    I'm trying to get a count of dates that are due but on condition that they are listed as being follow up. My countif for follow up works fine {=countif(Q2:Q,"*up")}, and my countif for overdue dates works fine {="Follow up (due = "&countif(J2:J,"<="&today()&" )")}, but no matter what I do I can't manage to combine them. Any help would be much appreciated.


    • Avi says:

      Turns out that the was a bracket in the wrong place:
      ="Follow up (due = "&countif(J2:J,"<="&today())&" )"
      works very nicely.

  59. Fayaz says:

    Just mailed you my sheet can you help me with that please

  60. Fayaz says:

    Hi Svetlana,

    Just mailed you my sheet can you help me with that please

  61. Shafin says:

    Svetlana Cheusheva

    I'm trying to get a count of common digit like (1234-2345-2789-1289-2548 = 2 100% 4 50% 8 50% 3 20%

    Please help

  62. Brandy says:

    Hi, Svetlana,

    Do you know if you can use countifs accross multiple worksheets? I'm tyring to count an occurance of a entry from a pick list, it occurs in the same column on each sheet but I can't find out how to reference multiple sheets?

    Any help would be greatly apprciated!

    • Hi Brandy,

      Just add a sheet's name with the exclamation mark before the range, for example like this:
      =COUNTIFS(Sheet1!A:A,1, Sheet2!A:A,1)

      This formula counts how many 1's there are in column A in Sheet1 and Sheet2 in the same rows.

      • Tushar says:

        Hello Svetlana,
        Iam impressed with your suggestions given above.
        below is the table for date & related days. I want count of total Saturdays. Means it should not consider repeated entries of same dates.
        My result should be 2.

        Can you help me
        3-Jan-15 Sat
        3-Jan-15 Sat
        10-Jan-15 Sat
        10-Jan-15 Sat

        Thanks in advance.

        • Alexander says:

          Hello Tushar,

          If you want to count only unique Saturdays, then you need to add a helper column to your table.

          Please enter the following formula into the 2nd cell of the helper column:

          Where A is the column with dates.

          Then just copy the formula down to the end. After that you need to sum this column, e.g. =sum(C:C)
          Where C is the helper column.

  63. Laura says:

    Hi Svetlana, I just sent you an email asking for your help with a formula. Thank you so much for all you do!

  64. Ali says:

    Hi there,

    Here is the formula I am working with that almost produced the desired result:
    =COUNTIF('Entity SPORTS'!$A6:$A20,"'Entity SUMMARY'!A6")+COUNTIF('Entity SPORTS'!$C6:$C20,"m")

    I want to count ONLY IF the first part is true AND the second part is true. Should I be using a different type of formula?

    To provide some context: On one worksheet I have a list of clubs (column a - club name is repeated as many times as there are people in the club), the names of the people in the clubs (column b), and their gender (column c, listed as M or F). I want to summarize the number of males and females for each club on a separate worksheet so that column A states the club name, column b will have the number of M, and column C will have the number of F.

    Any help would be greatly appreciated!

  65. karthi says:


    I have a doubt in excel. I have row from a1 to d1 with numbers or string and c1 have no numbers or string.

    what i am looking is when any cell is blank then the formula would return the text otherwise sum of all number from a1 to d1.

    reply or solutions much appreciated


  66. Wade says:

    Hey Svetlana Cheusheva, I am trying to count values in a column, but I would like to leave out all the blanks and 0 values. Any help would be appreciated.

  67. Anuj says:

    Hi - how can I find the LATEST TIME for different products. Product can be repeated multiple times. example:

    Prod1 - 12:30
    Prod1 - 13:45
    Prod2 - 07:00
    Prod2 - 16:00
    Prod1 - 09:00

    Now it should report Prod1 as 13:45 and Prod2 as 16:00.

    Thanks for your help in advance.

  68. Gespion says:


    Your example 4: The formula show B and C cells but you highlighted B and D cells in the printscreen.


  69. Gespion says:

    In cells A1 to A30 I have 2 alphabets A&E and in the BCells have values .I need a formula to count the no. Of values of A& E in the Bcell. Pl help

    • Hello Gespion,

      Try the following formulas:

      Sum values in column B corresponding to "A" in column A:

      Sum values in column B corresponding to "E" in column A:

  70. Abby says:

    Is it possible to change the value of a cell so that is counted as two as opposed to one? I'm using COUNTIF to calculate the number of hours each employee is working in a schedule, but some cells equate to 2 hours and others equate to 3.

  71. LePome says:

    I skimmed the comments and didn't see my situation listed:
    I want to determine the number of unique entries in a column (D:D) that fall between a pair of dates (say a1 and a2) where the dates of the events are in a different column (J:J).
    I know I can count all of the entries in the date range using
    =COUNTIFS(!J:J, ""&A2)
    and I know I can count all of the unique entries in D:D using

    How do I put them together?

    • LePome says:

      That didn't post right:
      COUNTIFS(J:J, ""&A2)

      • LePome says:

        It's not posting correctly, but hopefully you can interpret what I mean.
        COUNTIFS(J:J, ""&A2)

        Thank you for any help.

        • Hello!

          Regrettably, we don't know the way to fulfill your task using just one formula. You can either add a helper column or use a VBA macro.

          For example, you can enter one of the following formulas in cell K2 (if unique entries are checked by column D and date range):
          1 =IF(AND(J2>=$A$1,J2<=$A$2,COUNTIFS(D:D,D2,J:J,">="&$A$1,J:J,"<="&$A$2)=1),1,0)

          If you check for unique data only by column D:
          2 =IF(AND(J2>=$A$1,J2<=$A$2,COUNTIF(D:D,D2)=1),1,0)

          Just copy the formula to the other cells of the helper column and then sum the data in the column.

          • LePome says:

            Thank you for the quick and informative reply.

            • Kate Hoffacker says:

              I want to use coutifs to select some criteria and count the number of unique numbers (or text) in a column.
              Is this possible?

              eg I have a list of product codes and each code as a different column for the colour, I want to count the number of product codes (ie some are in more than once)

              Cheers, Kate

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


    Kindly do the neeful.
    I have emailed the sheet to you emailid.

  73. CH says:

    Is there any way I can use a formula that will add up different amount of rows but in the same column? Example. In cell AG12, I need it to have the sum of AF8:AF12, however, then I need AG15 to have the sum of AF13:AF15

  74. Gavin says:


    Im trying to do a sum of a range whilst looking at the date completed, for example....

    Qty Date
    5 02-Oct-14
    100 02-Nov-14
    5 02-Oct-14
    5 02-Nov-14
    4 02-Nov-14
    54 02-Dec-14
    5 02-Dec-14
    6 02-Dec-14

    Is there a formula give me the sum of left column against the factor Oct for instance and should bring back the result 10.

    Thanks for your help

  75. Giovanni says:

    Hello Svetlana,

    can you help please?

    I have a workbook referencing different tabs, on one sheet I have:

    Crew Manpower
    A 4
    A 4
    B 5
    C 3

    And on the other sheet I have:

    Crew Crew Member
    A John

    B Ted

    In the cell that states "Manpower" I wan to count the number of crew members according to the "crew" letter. I've tried nested COUNTA, IF, AND COUNTIFS and i keep getting errors.

    what am i doing wrong?

    Thank you!

  76. Juan says:


    Having some dificulties trying to figure out, how to count certain transactions that met 2 criterias City=Dallas and Type=SH.
    The table has multiple values and the following columns


    I have been trying using Countifs, with no success.

    Thanks a lot for your support.

    Juan Pablo...

  77. Takedia says:

    Is there way count cells when the date change? Say for instance I have a list people and their birthdays. Column A includes the names and Column B includes the birthdays. Is it possible to count how many people have the same birthdays? I believe I can do it birthday individually but is there is there a function that will automatically count them. I am do something similar to that however I have over 75,000 records to check. Is there that well automatically give me total after each birthday is changed. Like, if the previous birthday is not equal to the next date, display total of the previous birthday and reset total. Please advise. Thanks in advance!

  78. christine says:

    Ok. Because question 4 passed in mass i need to track earned sick time. I want to set up the sheet that if a cell is 30 the next cell =1 if 60 =2 and so on. What formula should I use.

  79. bhajan lal Sharma says:

    i have multiple mobile numbers where i want to check that how many time 1 Numbers coming but i am not able found however i have check with countifs but it not happening so please help me then how can check such type values in 10 digit mobile numbers

    Bhajan lal Sharma

  80. Kit Elloran says:


    I came across to this forum and it looks nice.
    I need your help in excel that would assign the same number in multiple rows example below.

    I need to do this in 58,000 plus records and really want to somehow automate the process.

    Col1 Col2 Results
    Kit 1 Kit 1
    Kit 2 Kit 1
    Kit 3 Kit 1
    Len 4 Len 2
    Len 5 Len 2
    Len 6 Len 2
    Len 7 Len 2
    Daddy 8 Daddy 3
    Daddy 9 Daddy 3
    Daddy 10 Daddy 3
    Amy 11 Amy 4
    Amy 12 Amy 4
    Amy 13 Amy 4
    Stephanie 14 Stephanie 5
    Stephanie 15 Stephanie 5
    Stephanie 16 Stephanie 5
    Mommy 17 Mommy 6
    Mommy 18 Mommy 6
    Mommy 19 Mommy 6
    Mommy 20 Mommy 6

    • Kit+Elloran says:

      Col1 Col2 Results
      Kit 1 Kit 1
      Kit 2 Kit 1
      Kit 3 Kit 1
      Len 4 Len 2
      Len 5 Len 2
      Len 6 Len 2
      Len 7 Len 2
      Daddy 8 Daddy 3
      Daddy 9 Daddy 3
      Daddy 10 Daddy 3
      Amy 11 Amy 4
      Amy 12 Amy 4
      Amy 13 Amy 4
      Stephanie 14 Stephanie 5
      Stephanie 15 Stephanie 5
      Stephanie 16 Stephanie 5
      Mommy 17 Mommy 6
      Mommy 18 Mommy 6
      Mommy 19 Mommy 6
      Mommy 20 Mommy 6

  81. Umer says:

    I need to calculate following Commissions on Sales how can i put in Formula

    <=20,000 = 1000 (Fixed)

    20,000 to 50,000 = 4%

    50000 to 100,000 = 3%

    100,000 to 200,000 = 2.5%


    200,000 and Above =2%

    Please help me ...

  82. NYT8er says:

    Maybe I missed the answer but need help in applying a formula to only count dates that are within the last year. My data is pulled from SharePoint Lists then using another tab I am able to count how many items are done based on each section (currently have 5 different sections)
    The formula I am using is:


    I would like it to only count the dates in Safety that are within the last year when the sheet is refreshed.

    Any help will be greatly appreciated.

  83. raed says:


    can you help please,,,
    i have five columns with differents informations and want to get all in another sheet by using only code, example:

    A B C D E
    100 SOHAR 50 25 2

    the information in the cells never change,,,can i make a formula ,when typing a code then get the other information in another 4 cells


  84. raed says:


  85. Jesu Dass says:

    Thank you for the details shared.
    I have 4 digit numbers like 1234,2345,2334, etc. i want to know when 4 comes as 4th digit which number comes mostly in 3rd digit as well as in the 1st and 2nd.
    there are thousands of numbers looking forward for your reply. it would help me a lot

  86. Count if problem says:


    I am currently struggling with this - I need a formula that works out the oldest date in A that does not have a processed date in Q.

    I have a formula that works out A =MIN(Audits!A7:A3026)but cannot figure out the COUNTIF for the second criteria, does anyone know if I am on the right track with a COUNTIF or am I just doing this wrong?


  87. raed says:

    as example,,in the first sheet as below,,,
    A B D E F
    5347 FALAJ AL-QABAIL F/S 15 16.74 1
    5294 FALAJ AL-QABAIL T 16 23.02 1
    5365 FALAJ AL-QABAIL N 17 23.02 1
    5727 SHINAS F/S 12 24.7 1.5
    5124 SOHAR BEACH F/S 25 24.7 1.5
    5530 SOHAR TREEF F/S 25 24.7 1.5
    5336 AL-WUQAIBA F/S 28 29.3 1.5
    5054 SOHAR SINAIYA F/S 29 29.3 2
    5299 SOHAR F/S 29 29.3 1.5
    5442 HUMAIRA F/S 30 29.3 1.5
    5211 AQAR F/S 45 38.51 2.5
    5438 AQAR TWIN FjS 47 41.86 2

    in the second sheet will be like as below,,,

    J k L M N
    5727 ? ? ? ?
    5442 ? ? ? ?
    5294 ? ? ? ?

    My question is ,,if i type the number which is i column "A" in the secound sheet in the column "J" how can i make a formula to get the information from
    column "B" ,"C" , "D" , "E" in the secound sheet in the column "K" , "L"
    "M" ,"N" , ???or whatever columns in any sheet even at the same one,,

    i hope my question clear to you

    Thanks in Advance

  88. zaheer khan says:


    so how can find highest number in excel sheet

  89. RC says:


    I want to count 1 column of data (contains £££) if another column contains a specific word.

    Could you help?


    • Hi RC,

      I am not sure I understand your task correctly. Anyway, if you want to sum cell in column A if a corresponding cell in column B contains a given word, you can use the following SUMIF formula:


  90. Mohammed says:

    Hi all,

    I Believe someone can help me here. what i want is from few different cell, i want to select certain letters or numbers. and want to to be able to link to one cell where it
    collect the selected letters and numbers from different cell and show as eg. cell 4 = (12345GB).

    eg. cell 1 = 0012
    eg. cell 2 = 4434
    eg. cell 3 = Good Buy

    i hope this is clear. its just so as i am inputting data on other cells and it generates like a ref if you like on the cell 4 as i instructed.

    please help?

  91. zak says:

    how would I write it sothat I could count a number if a condition is met EG

    and I want b1 to count where A2:A5 = Knife but use the totals in B2:B6
    So it would count = 9

  92. Denise H says:

    I'm trying to create a spreadsheet to keep up with employee's absences, tardies, etc. for a year. Each incident is not counted after a year. So I have a cell on top that says today's date and used =today() so that when I open the file the date will fill in. I have the dates in the first column, the second is tardies, leave early and vacation. On top under today's date, I want it to show the total Tardies, for a year (from today minus one year).

    Today's Date '=today()

    Tardies ??? <---- This should be a total for the year using
    Leave Early ??? <---- same as above
    vacation ??? <---- same as above

    Date Tardies Leave Early Vacation
    11/01/13 1
    11/02/13 1
    11/03/13 1
    11/04/13 1
    11/05/13 1
    11/06/13 1

    For Example: the ???'s above should add from today (11/2/14) to 11/2/13 and give me a total of 2 so that when I open the spreadsheet on a certain day, that information will automatically update and show me how many tardies, leave earlies and vacation days they have for that given year.

  93. RandyO says:

    I have a situation where the text that I want to count instances of is in various locations within the cells, e.g. A1 cell may contain 'Dog'; A2 cell may contain 'Cat, Dog', cell A3 may contain 'Bird, Cat, Dog'. I want to count all of the instances within the column that contains the word 'Dog'. It seems that no matter how I try to finesse this using COUNTIF and COUNTIFS with FIND and SEARCH etc. I can't seem to get this to work.

    Please help.

  94. RandyO says:

    Nevermind! I found the solution elsewhere.

  95. Dean says:

    I have a workbook with multiple worksheets of inventory from various departments. I'm trying to create a summary page that sums the different categories of inventory from each worksheet tab. For example, how many desks do we have in total.

    My worksheets are set up like this:
    Desks. 26
    Chairs. 57
    Cabinets. 1

    But the info isn't in the same cells on each worksheet.

    Is this possible? Im p.yaong with countif but can't get it to work.

  96. Shwan says:

    Hi There,

    I have tried multiple functions and nothing works!! I have tried COUNTIF, COUNTIFS, SUMIF, SUMPRODUCT and I cannot get the result I need!

    can you help.

    I am trying to count the number of cells which contain a certain Text before a date.

    I used this formula to calculate all cells within a month that belong to date

    but now I need the first criteria to be a text specifically "DT*", and the second to be before november (<11)

    can you help?

  97. Kali says:

    I have a set of customers and their ages in the Rows. In the Column section, I have a set of ranges from: <12months, <36months, <3years, 3 and <7years, 7 and <17 years, 17 and <40years. I want to add a "1" down the columns for each customer that is <12months, or less than 3 years, etc...Then I want to count how many of them are in that age group and get the total for each age range. May you help me please?

  98. Ryan says:

    I am trying to use countif to return a value of less than or equal to 15 for a range of cells in column A. It keeps returning a zero result. My formula looks like this : =countif(A3010:A3190,"<=15"). Does it matter that my A column is actually a formula that is resulting in the numbers being displayed?

  99. KC says:

    Hi there - hoping you can help please!

    I have two columns

    Total Sessions Location

    1 S

    10 B

    5 S

    6 B

    what is a countifs formula to reference the location and return and count the sessions please?

    many thanks

    • Hi KC,

      If my understanding of your task is correct, you need a SUMIF formula rather than COUNTIF. For example, the following formula will pick all "b" locations in column B and return the total of corresponding sessions (column A):


      In your example, the result will be 16. If you are looking for something different, please clarify.

    • mandy says:

      hi i am stuck in a prob.
      i want to count the two digits numbers in a given data.
      i applied =ccountif(RANGE,LEN(RANGE)>2)NOT WORKING

  100. Kowshick says:

    Hi, i have some formula populated column.

    I was trying to do a COUNTIFS(,"") to find the data which condition false.

    But i understood that COUNTIFS takes the formula as a value and gives a erratic value.

    Can you guide?

  101. Dan says:


    Column A: dates
    Column B: numbers

    I would like to calculate the number of times values in column B within a specified range occur within a specified date range. I've emailed an example file to support.
    Any help would be much appreciated.

    Many thanks,

    • Hi Dan,

      I believe the following formula will work for you:
      =COUNTIFS($B$2:$B$75, ">=6", $B$2:$B$75, "<=7", $A$2:$A$75, ">="&F2, $A$2:$A$75, "<="&G2)

      I emailed you the worksheet with the formulas a moment ago. Hopefully, this is what you are looking for.

  102. Rasel says:

    How can I solved to multiple word in a criteria from single word in a excel file. pls help me

    • Hello Rasel,

      I am sorry, your task is not clear. If you can describe it in more detail and give some example, we'll try to help.

      • Rasel says:

        Thanks for your replay......
        Actually I would like to say,
        which formula should I use from multiple condition but value will be text.
        For example: sumifs functions.
        If I use to sumifs formula then it result any data but it not be text.....
        Please help me if you cooperated me..

  103. satheesh says:

    A B C
    CT Vinod Singh 22
    CT Rajesh Kumar Purohit 24

  104. wilfy says:

    i need a formula to automatically check mark i.e(ü) mark a cell(eg:A1) if another cell (eg:H1)contains number:1
    Condition is H1 may contain any numbers like 1,2,5,6,8 to 20
    A1 should check mark only if there is 1
    ( if i drag formul A2 should check mark only if there is 2...and process upto A20 i.e number 20)

    Formula should come like this:
    (A1:A20) should Check H1 if perticular number contains in that cell i.e
    check H1 if it contains 1,3 then tickmark A1 A3
    check H1 if it contains 2, 20 then tickmark A2 A20
    check H1 if it contains 3 then tickmark A3
    upto 20numbers

    Formula should come like this:
    (B1:B20) should Check H2 if perticular number contains in that cell i.e
    check H2 if it contains 1,3 then tickmark B1 B3
    check H2 if it contains 15 then tickmark B15
    check H2 if it contains 18 then tickmark B18
    Upto 20 numbers

    I CAN TAKE H1 I1 J1...

  105. Wayne says:

    Hi, hope you can help. I'm trying to get a cell give me a figure of the following(Cell A is First Aid Course and Cell I says they attended or not attended) - I don't want it to count if the cell is blank (so basically a upcoming course)

    Course Title Start Date End Date Tutor Customer Name Attended?
    First Aid 01/11/14 14/11/14 John Paul Jones Attended
    First Aid 01/11/14 14/11/14 John Sarah Smith Attended
    First Aid 01/11/14 14/11/14 John Mo Ali FTA
    Food Safety 11/11/14 15/11/14 Lee Paul Jones Attended
    Food Safety 11/11/14 15/11/14 Lee Sharon Jones FTA
    First Aid 24/11/14 31/11/14 John Steve Bi
    First Aid 24/11/14 31/11/14 John Simon Gee

    Result to go into this type of box
    Course Name Total Referred Attended FTA
    First Aid ? ? ?
    Food Safety ? ? ?

    I can send on the sheet if its easier.

    Thanking you in advance


  106. Wayne says:

    Sorry it hasn't posted correctly - just looks a mess

  107. Chalinda K says:


    Below is my issue. I have a few statuses which can be used to a closed ticket. I want to count all the closed tickets in the worksheet using the status. I used below formula but it did not work. Please help..

    COUNTIFS(A2:A1000,"A",B2:B1000,"Night",C2:C1000, OR("Solved","Cancelled",....))

    Department Shift Status
    A Night Solved
    A Night Cancelled

    Thanks in Advance!

  108. Bil says:


    I want to create a formula that counts how many of the letter 'y' occurs in particular columns but not in a range. The columns I want to search are D2, F2, H2, J2, L2, N2, P2, R2, T2, V2. The problem I'm encountering is that I don't want to search E2, G2, I2, ... I then want to have this formula for all rows in the spreadsheet. I don't see the answer above ... Thanks!

  109. Jon Fuller says:

    I want to be able to check if data in one cell in column a = x and then if so count the data in adjacent cell in column b.

    To explain in more detail, I am creating a statistics chart where commissioners need to be able to be able to compare data for region 1 to region 2 and region 3.

    So column A will contain region keys such as 1, 1, 1, 2, 3, 3 then in column B will be ages. So if column A contains a 1 collect data in cell adjacent to the cell it is counting.

  110. Shwan says:

    Hi! Here's the data I want to process. I want to count the number of entries a specific person submitted that matches anything except Not Qualified.

    DECLINED Peñalosa, Joyce Ann
    NOT QUALIFIED Calvo, Vivian
    CALLBACK Roda, Scepter John
    NOT QUALIFIED Henardino Jr., Edwin
    NOT QUALIFIED Asentado, Darell
    CALLBACK Peñalosa, Joyce Ann
    DECLINED Roda, Scepter John
    CALLBACK Alipio, Marisel
    DECLINED Emeterio, Absalon
    NOT QUALIFIED Solomon, Cinderella
    NOT QUALIFIED Solomon, Cinderella
    CALLBACK Toring, Jared
    CALLBACK Henardino Jr., Edwin
    NOT QUALIFIED Viscayno, Mabel
    NOT QUALIFIED Viscayno, Mabel
    DECLINED Solomon, Cinderella
    SCHEDULED Roda, Scepter John
    CALLBACK Emeterio, Absalon
    NOT QUALIFIED Henardino Jr., Edwin
    NOT QUALIFIED Trocio, Princess Joy
    CALLBACK Baruel, Coleen Grace
    CALLBACK Baruel, Coleen Grace
    CALLBACK Baruel, Coleen Grace
    NOT QUALIFIED Toring, Jared
    SCHEDULED Solomon, Cinderella
    SCHEDULED Gamboa, Estrella
    DECLINED Peñalosa, Joyce Ann
    DECLINED Calvo, Vivian
    NOT QUALIFIED Alipio, Marisel
    CALLBACK Baruel, Coleen Grace
    CALLBACK Arizobal, Argin
    DECLINED Calvo, Vivian
    NOT QUALIFIED Arizobal, Argin
    CALLBACK Toring, Jared
    DECLINED Emeterio, Absalon
    SCHEDULED Peñalosa, Joyce Ann
    NOT QUALIFIED Henardino Jr., Edwin

  111. M2 says:


    My countif formula returns 0 if it does not anything that matches the criteria. Is there a way to set up the formula so that if it doesn't find anything that matchs the formula returns "-" instead of 0.

    Thank you

  112. Hasan says:

    I like to count First five character as a individual number like; 98765_A3(98765),98765_A6(98765),87654_A2,(87654),92345_A1(92345) in a column but in different row and same numbers count once only. How can I do this?

  113. Hasan says:

    I like to count First five character(number) as a individual number in a column but in different rows and same numbers count once only. How can I do this in excel? Fore example;

    A B
    98765_A1A2A3 ISO
    98765_A4 AATCC
    87654_A2A3 AATCC
    92345_A1A3 ISO

  114. Adnan says:

    Dear All

    I need urgent help on one formula. For Eg

    A B C D E
    AXT-11232 2,500 89369 sent

    I want in column E the current date if any of the cells like A, B, C or D are filled. My data will be in the same format as in A, B, C. I just want the current DATE , whenever data has been entered in any of the cells marked as A, B , C or D

  115. amid says:

    I have only one column
    how to count how many row have time 7 am to 8 am and next 8 am to 9 am and next?


  116. Vil says:

    Hi, for my case, cell A1 has an entry of 19/11/14 and cell B1 has an entry of 25/11/14. 25/11/14 is more than 19/11/14 by 6 days. I would like to set the formula for the dates that are more than 5 days to be false. Which means the entry above is false because it is 6 days.

  117. mathieu says:

    i have a question, i am making a personnel planning for my employees. This is based on competences this means that specific people are trained to do job A but they can not do job B. I have 2 tables. One table "Training" with the name of the person and in the same row for which jobs they are trained so job A, job B, job D. Then i have the real personnel planning. I have on top which job needs to be executed so for example job A. Hence I put a name of the person. I want to have the cell highlighted in Red if the person is not trained for job A. I think to do it via COUNTIF but i am confused as I have different criteria, i need to refer first to job A, hence i need to search if that specific person is trained for job A. Can somebody help me please?

  118. Rob says:


    I am trying to count the number of days marked "Holiday" in a set range but only those with a date less than 31/05/2014 but greater than 31/03/2014. The date range is in column R with the text fields in columns T:X.

    The other point is that the date column only has a week commencing date, not a date for every cell.

    I have tried =COUNTIFS($R:$R, "<="&I30,$T:$X, "Holiday")(where I30 is a date field with 31/05/2014). This produces an #VALUE! error. I haven't tried to enter the additional date criteria as I wanted to get the formula to work first.

    Taking each part in isolation (=COUNTIF($R:$R, "<="&I300 and =COUNTIF($T:$X, "Holiday")) works perfectly but not combined.

    Can anyone point me in the direction of where I'm going wrong?

  119. Bob R says:

    Hi Svetlana
    I am trying to count the number of time a postcode is used within some 35,000 records. The postcodes have the format 'AB10 9BC' and I only wish to count the first group of figures. Using =COUNTIF(G:G,"AB10*")seems to work fine BUT.... Some of the first group of numbers consist only of three digits eg 'AB1 9BC'. This means that counting for AB1* also includes and counts AB10/AB11/AB12 etc etc. How can I count only AB1 postcodes in this example? Hope that makes sense and I look forward to your reply.

  120. liam says:


    i need help to be able to use the countif function or similar to count text as 2 instead of just one is this possible


  121. Becky says:

    Is it possible to use the COUNTIF option when using fractions? I have a column of several lengths (32", 36", and 48"). I know the formula to count the cells - but I'd like to count the cells in the same column that only have 32" lengths in the cell. I tried the following and it did not work.

    Thank you!!!!!

  122. Michael says:

    I am trying to count the number of occurrence of which the two certain conditions must be met. I tried it this way

    COUNTIFS('Brgy Cases,other Agencies'!$C$11:$C$13,"*Alangan*",'Brgy Cases,other Agencies'!$V$11:$V$13,"*Those Issued*") + COUNTIFS('Brgy Cases,other Agencies'!$C$15:$C$17,"*Alangan*",'Brgy Cases,other Agencies'!$V$15:$V$17,"*Those Issued*") + COUNTIFS('Brgy Cases,other Agencies'!$C$19:$C$21,"*Alangan*",'Brgy Cases,other Agencies'!$V$19:$V$21,"*Those Issued*") + COUNTIFS('Brgy Cases,other Agencies'!$C$23:$C$24,"*Alangan*",'Brgy Cases,other Agencies'!$V$23:$V$24,"*Those Issued*") + COUNTIFS('Brgy Cases,other Agencies'!$C$26:$C$27,"*Alangan*",'Brgy Cases,other Agencies'!$V$26:$V$27,"*Those Issued*")

    You can notice that there is 5 COUNTIFS function, and it's working fine. Unfortunately, when i am trying to add another set of countifs, the result returns a date format.

    Is it because there is a limit for summing up the occurrence in COUNTIF limited only to 5?

    Thank you.

  123. satheesh says:


    1 CT MUNNA 18-06-1991 23 17
    2 CT MITHUN 28-02-1991 24
    3 CT BISWAJIT 26-06-1991 23
    4 CT KARTICK 14-10-1989 25
    5 CT SOURAV 22-10-1991 23
    6 CT VIKASH 28-02-1991 24
    7 CT VEERENDRA 03-01-1992 23
    8 CT OM PRAKASH 01-07-1989 25
    9 CT PAWAN 02-01-1990 25
    10 CT SANJAY 09-01-1990 25
    11 CT SUNIL 21-10-1990 24
    12 CT Atanu 10-07-1991 23
    13 CT KALI 05-02-1990 25
    14 CT RAKESH 05-01-1991 24
    15 CT SUNIL 09-02-1991 24
    16 CT PUPINDER 12-12-1991 23
    17 CT PATIL 23-11-1991 23
    18 CT Gulab 10-07-1991 23
    19 CT Bhupendra 18-12-1991 23



  124. Amit Priyadarshi says:

    I have a document where one column has list of name (around 100). Then I have so many other columns which can have any one value from set of 5 values(val1,val2, val3, val4, val5).
    Now I want to count number of val3 in row where name is Name12.
    Now to make is dynamic I have created 2 cells
    1. First to select Name (data validation = list)
    2. Second to select val (data validation = list)
    Now I want the next row to calculate total cell where value = value of second cell but from the row where name = value from first cell.

  125. Ben says:

    Hi Svetlana, I've read most of these question and can't find one with the same problem as I'm having. I have a range, for example A3:A9, and I want a result when another cell contains text from one of the cells in the range. For example if A3="cat", I want a result when, for example, C2="a cat on a mat". If the situation was reversed, and I wanted to know if the contents of a cell was contained in a range, I'd do =COUNTIF(A3:A9,"*"&C2&"*"), but I want to know if any cell in a range contains text in a cell. My problem is I can't use add anything, like a wildcard, to a range, so I can't do =COUNTIF("*"&A3:A9&"*";C2)

    Is there a way to get this result?


  126. Chandan Tiwar says:


    i have a excel sheet where many of products like 20p,6P,2D
    and i want to sum of products in next sheet againts product when i filter any where in main product sheet

  127. Marcell says:

    HI there.

    I'm not well versed in COUNTIF, but I think this is what I need but not sure how to go about creating a formula.

    I have column with a $$ amounts in each cell and the corresponding column/cell would either be a Yes or No.

    I need to create a grand total of all Yes' and a separate grand total for No's.

    Can you help?

    Thanks in advance.

  128. May says:

    Formula that will continuesly count based on Cell value and number it sequencially



  129. musharaf ahmed says:

    I have following data sheets.

    Branch Code Risk Number of high risk No of Risk Instances
    0101 H 04 18
    0102 M 11 20
    0103 H 9 24
    0104 L 5 13
    0105 M 10 26

    How I can fix formula that if branch code = 0101, check risk, count number of high risk and multiply number of risk instances

  130. Melody says:

    Hi Svetlana,

    I have been following your posts and find them very helpful.
    I just need help on something:

    I have a running list surgical procedures for 2014.
    Col A = date (running list)
    Col B = Types of Procedure (Procedure A, Procedure B,...)

    I want to count total number of procedures per month:
    January - How many Procedure A, B, C, D...?
    February - How many Procedure A, B, C, D... and so on.

    Thank you.

  131. Bhagesh says:

    Please Help.

    I have tree sets of data. Project Type, Resource Name and Days (columns Jan-14 to Dec-14).

    Project Type being.. T1, T2, T3, T4, T5.
    Resources X, Y, Z.....
    Resource Allocation Days against each month... 17, 19, 11, 20, 20 as values

    Now I need to calculate in a summary table.. Count of Months resources are allocated to a Project Type.

    Can you please help me.

  132. Dan says:

    I have a time keeping system that is putting a "'", an apostrophe, in a a group of cells. I am trying to count the number of people that are in a range, and unfortunately the range also contains the apostrophes in otherwise blank cells. So I need to subtract the number of cells that contain the apostrophes from total number of cells. The COUNTIF does not recognize the apostrophe, I assume because it thinks it is a character to treat the cell like text rather than a date, or number, etc. (the only work around). I cannot figure out a way to fix this other than "clearing" all of the cells of apostrophes, which is very time consuming and error prone. Any ideas?

  133. Mohamed says:

    I want to use Countif formula to test the criteria range against a specific logic. In other words, If column A contains a date, and I want to count the cell if its date is within a certain week number. What I did is =COUNTIFS(Violations!A:A,WEEKNUM(Violations!A:A=WEEKNUM(TODAY())))

    But it returned 0 values. Would you please check how can I do it?

  134. Mak says:

    Thank you very much.. really it's very helpful

  135. Lynda says:

    I need to use a function to count the number of Part in column c2-c134 in the NWest which is in column f2-f134, I dont seem to be able to enter the correct data into any function to get it to do this

  136. Nikhil Nanwani says:

    I need the function to count the different condition in a single range. Like, In a single range numbers given 1,2,3,4,5,6,6. so, now i want to know in this range that how many numbers 1 to 3 are there.

    example: 1,1,4,4,5,6,6. answer is 2. because in this condition 1 is two times repeating from 1 to 3 series and 6 is also 2 time repeting in 4-6 series.

    Please help me.

  137. Troy says:

    I trying to have excel calculate how many times I get as close as possible to 1 with out going over, within a column of numbers in my spread sheet? Can you please help. My column is about 40 cells in length and the unit of measurement I have are random.
    Example numbers - These number will be changing more then once.

  138. sarwar says:

    Hi i have a problem please help me to get out this problem
    56 658 658
    65 58 258
    256 2 25
    3 88 635
    365 895 65
    987 65 658
    i want to count cell which have only two digit (example, 56,65,25 etc)

  139. Luke says:


    I am trying to make a spreadsheet that will count the number of times "A" happens between "B" occurrences. I am not really sure how to explain it so here is some info of what I am trying to accomplish.


    So for the above data, I would like to count the number of times "A" appears between any given range of "B" values. So for the first two instances of "B" there would be "1" and for the range between the second and third instances of "B" there would be "4" and "null" would not be counted (they are blank cells).

    Thank you for your help.

  140. Sameer says:

    Dell kiran 200
    Dell sameer 400
    Dell kiran 300
    Hp amol 200
    Zenith mohsin 500
    Dell =countif ans 3
    I want to calculate the data based on column b but calculate data form d column.I want answer if there is three dell record I want count the data
    from d column that answer Is three based on dell records.
    which functions is use for calculation
    please help me

  141. Zakk Baker says:

    Hi There,

    I would be deeply grateful if anyone can help me with my issues!!!

    I am trying to set up my spreadsheet to enable me to calculate how many "1's" fall on the month of january.


    Date Error Code(s)
    01/01/2015 1
    02/01/2015 1
    03/01/2015 1
    04/01/2015 1
    05/01/2015 1
    06/01/2015 1
    07/01/2015 1

    The idea is, I will have a break down of the error codes for each month!
    I've been trying all sorts of COUNTIF formulas but have had no luck!
    Please help, I'd be eternally thankful...

  142. Tracy says:

    Am I just overlooking the obvious, or is there just not an easier formula to use to count all the cells within a column with a value that is a multiple of 5, other than:


    Listing every multiple of 5 is not even possible so surely there is another way??

    PLEASE PLEASE help - even if the answer is simply "no". -Thanks!

  143. Karen says:

    Hi there, I'm very sure my problem will be easily fixed by one of your Excel aficionados but I'm intermediate level at best. I have complied a huge roster. I had 6 dentists and we've just taken on another 2. In the past I have manually adjusted the surgery number up to our max of 6. This has now increased to a max of 8. Can I use a countif to calculate the number of surgeries in use using the Dentists initials as criteria and increasing in increments up to 8? Rather than entering these numbers manually and adjusting if someone goes on leave etc?
    1 TT 2 SL 3 AB 4 JM 5 TL 6 JD ?CB ?PD

  144. scott says:

    Hi i've been driven mad by excel today I hope you can help.

    I'm trying to make a critera with COUNTIF statement before my SUM value kicks in.

    As you can see the calculation works on anything less than 12 I'm trying to get cell K15 to stop counting after 12 unfortunatley K15 will have data larger than 12 so 12 would be capped if you like. is this possible?


    Any help would be appreciated

  145. Rasel says:

    How are you, please help me to my below problem.
    Which formula should I use from multiple condition but value will be text.
    For example: =SUMIFS(A:A,B:B,"Line",C:C,"Date")
    result factor=205 (suppose).
    But I want to the result "text", no "digit".

    So, how will I get the result "in word".....
    Please help me if you cooperated me.....
    Thanks in advance

  146. wan says:

    could you please advice, how to use formula if J8 contains multiple score with texts to be match with L8 (contains scoring 1,2,3). So I want if I choose/click J8 then L8 will displaying match score. Thank you

  147. Jaikumar says:

    please advise how to use countif formula to use =COUNTIF(B6:B51, $A$54)/COUNTA(B6:B51)- every day i need to add one coloumn in B5. however it should automatically calculate this week as B5:B52, but B5 is not getting calculated.. Thankyou

    • GRS6411 says:

      A B C
      1 Start Finish Days Open
      2 12/10/2014 12/13/2014 3
      3 12/15/2014 12/16/2014 2
      4 12/16/2014

      I am using =NETWORKDAYS(A2,B2) for days open
      How can i modify the above formula so it will pickup Todays's date if there is no date in B5.
      Can anybody help ASAP with that.........

  148. Rick says:

    I am trying to count two criteria. One column is counting all "New" occurances if column two is equal to a range of 15 different zip codes. I know I can countifs (a2:a20,"NEw",....) but the second criteria is d2:d20,g1:g16. I have put G1:g16 in as an "or" condition but keep getting a "0" count. Basically I want the thing to countif a2:a20 is new and d2:d20 "is one of" several different numbers. I was trying to do it without a lengthy formula referencing each number in its own countifs formula. Can you help?

  149. Anonymous says:

    i want to count if enter one score (numbers) for one day for one employee and need to count if enter next whenever coming days that figure need to added

  150. Tracy says:

    Is there not an easier formula to use to count all the cells within a column with a value that is a multiple of 5, other than:


    Listing every multiple of 5 is not even possible so surely there is another way??

    PLEASE help if possible......somebody....anybody? -Thanks! :)

  151. Raj says:

    let's say i have 200 names in column with some names repeated in the list. How can i get the count other than 2 specific name?

  152. arvind says:

    dear team

    my problem is tat a cell contains a value in general format as for eg; 2085 when i count using this countif formula the value does not get matched up and doesnt return 1 to me simply as zero

    When i entered into that cell it contains some extra characters as space if i try to delete that space and put enter the last value i.e from "2085" 5 has been truncated and im left with 208
    can u pls help me

  153. Costin says:

    Col.A Col.B
    1 A
    2 B
    3 C
    4 A
    5 D
    6 C
    7 A
    8 B
    9 D

    I would like to extract from above table in a separate table like bellow the corresponding values with comma.

    Col.B Col.A
    A 1, 4, 7
    B 2, 8
    C 3, 6
    D 5, 9

    Thank you in advance!

  154. vikash gupta says:

    i have 500 account number
    how to search double

  155. SUNIL KUMAR says:

    Dear Sir/Mam,I wanna to count objects in different cells of the same row/col., I have try so many defferent formulas,but I fail to do so,Help me.
    My Question is to add defferent cells of same row/col.
    Example:- Count objects in C1,C5,C10,C15..........,there is no range like c1:c15,so plz help me.

  156. Elodia says:


    I have a spreadsheet i am trying to count how many times a value (i.e. 3) occurs within cells E8:G380 when the text value in column D is ABC. If I use: =COUNTIFS(D8:D380, "ABC", E8:G380, "3") it returns a value error. Excel also only allows me to use two countif or countifs functions per cell.
    Any help is appreciated!

    Thank you!

    • Hi Elodia,

      All ranges of the COUNTIFS function must be of the same size, i.e. must have the same number of rows and columns. You can use the following formula instead:

      =COUNTIFS(D8:D380, "ABC", E8:E380, "3")+ COUNTIFS(D8:D380, "ABC", F8:F380, "3")+ COUNTIFS(D8:D380, "ABC", G8:G380, "3")

  157. Jamie Booth says:

    Hi, Looking at the COUNTIFS with multiple criteria for dates....

    Is there an adjustment I can make to this formula.....

    =COUNTIFS(C2:C9, ">=6/1/2014", C2:C9, "=C1","<=C2"
    So, translation - "count everything here, that match this, between this date and that date".

    I need this to make it more user friendly than expecting people to manually adjust a formula in a cell.

    Either in the formula or with VBA?


  158. Jamie Booth says:

    That last post missed a whole chunk of something I wrote so it now probably doesn't make sense...

    I meant: From this
    =COUNTIFS(C2:C9, ">=6/1/2014", C2:C9, "=C1","=C1","<=C3")

    Hope that makes more sense and posts what I wrote.....

  159. Jamie Booth says:

    Ah. no it didn't. Waste of time!!

    • Jamie,

      Sorry for this, none of the blog engines we've tried is perfect.

      As for the formula, you should not use cell addresses inside the quotes, otherwise Excel interprets them as mere text strings. For example, instead of "<=C1" you write "<="&C1.

      Try the following formulas:

      =COUNTIFS(C2:C9, ">=6/1/2014", C2:C9, C1)

      =COUNTIFS(C2:C9, ">=6/1/2014", C2:C9, "<="&C1)

  160. Sara says:

    I hope you can help me. I am trying this formula as follow.

    =COUNTIFS('Sheet1'!$AV:$AV,""&"",'Sheet1'!$AR:$AR,"*Feb*") +

    I need to know contacts per month. If AV is not empty and AR in sheet1 / AN in sheet2 have a month, count it( they could be empty, so count only the ones that have months on them ). As simple as that.

    It doesn't return any value when I use ""&"", or "*".
    The format of both is "text" and the range as is the same.
    It is not working and I can not see what I am doing wrong.
    thank for helping!!

  161. Sham says:

    I am working on a spreadsheet with a basic countif formula thats working well for me. When I add a filter to the worksheet in column A and filter by one item (e.g Apple) the formula does not autmatically updated. Please help.

  162. RichardR says:

    1202 13
    1401 110
    2107 121
    1108 140
    1111 154
    1103 210
    2102 812
    2103 1103
    1208 1104
    1014 1105
    1107 1111
    1105 1154
    2308 1204
    1206 1210
    n/a 1231
    1154 1401
    2305 2101
    1306 2102
    2101 2103
    1204 2107
    1201 2201
    1210 2302
    2302 2308
    1104 (blank)

    I have two column's of number.
    Column1 is actual numbers
    Column2 is numbers that have been input manually

    I want to use a countif function to tell me how number in Column2 are not in Column1

    • Hi Richard,

      The task requires creating a helper column, say Column C. enter the following formula in C1 and then drag it down to other cells:
      =IF(AND($B1<>"",COUNTIF(A:A,$B1)=0), "Not in Column 1", "")

      After that, you can use the below formula to count how many numbers in Column2 are not in Column1:
      =COUNTIF(C:C, "Not in Column 1")

  163. Joanna says:

    I am trying to count the amount of cells that list "Baltimore" in column H and the Agents name in column F. So basically how many lines in a sheet that contain a name AND a particular client.

    I tried =COUNTIF(Eval_Data!F:F,BRL_Agent_Summary!C4)+COUNTIF(Eval_Data!H:H,”Baltimore”)

    I fount that this will count the agents name but every instance that agents name appears and also if Baltimore appears.

    I also tried =COUNTIFS(Eval_Data!F:F,"C4",Eval_Data!H:H,”Baltimore")

    But it isn't counting the cells I want. It is bringing back 0 when I know there is data. I'm not sure what is wrong.

  164. Jennifer says:

    This time with name...

    I need to be able to count the number of columns that have a date that is less than 11 days old. I understand I could do this using the Today() function, but it keeps hanging. I currently have =COUNTIFS(Infrastructure!$A$2:$A$2000,"Infrastructure",Infrastructure!$C$2:$C$2000,"<11"(&TODAY()-$C2)
    Where infrastructure is the name of the tab to go to, the first criteria says if the cell is 'Infrastructure', then the problem is the last calculation. I want to count the number of occurences where today minus the date in cell c2 is less than 11. CAn you help?

    • Hi Jennifer,

      If my understanding is correct, you need just need to tweak the formula a little bit:

  165. Terry says:

    I am trying to count the number of Army in one column that is due to depart between a date range of 90 days in a separate column. I have tried =COUNTIF(I:I,="ARMY")+COUNTIF(Y:Y,">="&TODAY()+1)-COUNTIF(Y:Y,">"&TODAY()+90). I get the correct number for the dates in the range, but doesn't exclude Air Force, Marines, and Navy. I hope you can help. Thank you

  166. hanif says:

    Hi There I wonder if you can help me with COUNTIFS for text Values:

    I have NATIONALITY in row G, and TYPE OF VISA in row J,K,L,M,....(contains "1"/symbol "x" as value).

    I use =COUNTIFS(G12:G117,"=NATIONALITY",J12:J117,"1",......) and it works.

    now if i want to apply the formula to a new items, how is the fastest way to do that?

  167. Joao says:

    Hi i need to count values in a colum condicioned by another colum, just like a i have XX,YY and ZZ in colum A and Values in Colum B i need to say cout at colum B if at colum A is YY how can i do it? Need help

  168. Tim S. says:

    Is there a way to count the number of instances in a column based on a criteria and then display the results not as a number, but as a list of data from the cells that meet the criteria?

    Such as:
    Lenovo User 1 Serial#
    Lenovo Inventory Serial#
    Apple Inventory Serial#
    Apple User 2 Serial#
    Apple Inventory Serial#
    Microsoft User 3 Serial#


    But I would like to display the rows where the data meets these criteria, instead of the number of instances.

    Desired display in a separate part of the spreadsheet, while not deleting the original cells.
    Apple Inventory Serial#
    Apple Inventory Serial#

    Thank you very much for any assistance, positive or negative.

    Timothy Scully

  169. Mahyar says:

    Hello Miss Svetlana Cheusheva,
    I want ask you about How do I Sum or ets in one cell. I have 2 cell (A1)(A2), in cell one(A1) have number that I enterd, an other cell (A2) I have condition is that: if A11.000.000*3. It means between 2 Numbers that I imagin

  170. Farooq says:

    Hi Svetlana,

    I have two strings FirstRow & LastRow defined as address ranges

    How can I use CountIFS formula to count number of zeroes between these two ranges.

  171. Satya says:

    I am trying to use the COUNTIFS formula

    I want to count the numbers of different interest rates in one column and produce a result if it satisfies the criteria that it belongs to a field called MN (there are various other fields..

    eg COUNTIFS(('PD11'!$H$8:$H$177,"<6%"),('PD11'!X8:X177,"=MN"))


  172. hemant sharma says:

    Thanks for Guide.

  173. hemant sharma says:

    Svetlana Cheusheva you are so beautiful

  174. Elvis says:


    please help me count the same value from different cells in the same row. The cells I need to be able to choose.

    What I need to calculate is the attendance for each student but on two different occasions. So I will have to choose every second cell and thus it can't be a range.

    Sun. Dec 21 Mon. Dec 22 Tues. Dec 23 Wed Dec 24 Thurs. Dec 25 Attendance
    P P P A A A P P P P 0
    P P P P P P P P A A 8

    As you can see, it is Communication Skills and Reading and Writing Skills that the student attends on two different times.

    I tried

    =COUNTIFS(D8,"=P",F8,"=P",H8,"=P",J8,"=P", L8,"=P")

    and many other formulas with no success.

  175. Roshin Stephen says:

    Hi Svetlana,

    Please help me get a formula for my query..

    In an excel sheet having 3 columns- 'Date', 'Time' & 'Amount'. I need a formula which will add only those amount in the 'Amount' column, where the Date is from A to B & Time between and equal to C & D.

    Please can you help me.?

  176. Ruman D says:

    Hi, Firstly, I thank you for providing this excellent service. You are doing a great service.
    I need some help with this. I want to send/link cells or rows or columns to another worksheet/book when the columns meet certain criteria.
    For example, if the date and time attended in column D and E is later than a day or 10 hours compared to column B and C, and link those data that match criteria to another sheet.
    Thank you and hope to hear from you soon.


  177. Rosman says:

    I have a problem regarding counting multiple criteria and the problem is counting only the "year" my data date like 29/05/2014.

    =COUNTIFS('Rabbit Inventory'!$F:$F;"Buck";'Rabbit Inventory'!$E:$E;$AG5;'Rabbit Inventory'!$Q:$Q;"Purchased"; 'Rabbit Inventory'!$R:$R;YEAR(2014))

  178. Anonymous says:

    Hi any one can help me on the following
    Peter 02/01/15
    Hari. 05/01/15
    Sam. 12/01/15
    Peter. 13/01/15

    =countifs(a:a,Peter, b:b,"="&today-3) but I suppose to get 1 ryt if today date is 13/01/15??

  179. Giwrishankar says:

    Can you send me the sample excel, how to calculate & I need the count of net working day

    Example: In a monthly if I used to get "n" number if volumes... I want to calculate the ageing.... But I don't want to calculate Saturday & Sunday...

    Can you support me to get the the format sample through mail

  180. Pauline says:

    DATE Service sum(totalHits) sum(Successful) sum(Dispatched) sum(Failed)
    11/1/2014 MPESA Uploader 373 285 =COUNTIF:(sheet1:status1,"<=103", sheet1:status1,"<=202")
    11/2/2014 MPESA Uploader 227 158 1
    11/3/2014 MPESA Uploader 285 284 1
    11/4/2014 MPESA Uploader 294 294 0
    11/5/2014 MPESA Uploader 301 301 0
    11/6/2014 MPESA Uploader 253 251 2
    11/7/2014 MPESA Uploader 274 273 1
    11/8/2014 MPESA Uploader 283 281 2
    11/9/2014 MPESA Uploader 2 2 0
    11/10/2014 MPESA Uploader 268 268 0
    11/11/2014 MPESA Uploader 7 7 0
    11/12/2014 MPESA Uploader 58 57 1
    11/13/2014 MPESA Uploader 243 243 0
    11/14/2014 MPESA Uploader 195 195 0
    11/15/2014 MPESA Uploader 259 259 0
    11/16/2014 MPESA Uploader 299 296 3
    11/17/2014 MPESA Uploader 291 289 2
    11/18/2014 MPESA Uploader 342 342 0
    11/19/2014 MPESA Uploader 335 335 0
    11/20/2014 MPESA Uploader 394 393 1
    11/21/2014 MPESA Uploader 304 302 2
    11/22/2014 MPESA Uploader 295 295 0
    11/23/2014 MPESA Uploader 308 308 0
    11/24/2014 MPESA Uploader 625 617 8
    11/25/2014 MPESA Uploader 361 361 0
    11/26/2014 MPESA Uploader 295 295 0
    11/27/2014 MPESA Uploader 356 356 0
    11/28/2014 MPESA Uploader 262 262 0
    11/29/2014 MPESA Uploader 227 227 0
    11/30/2014 MPESA Uploader 358 358 0
    I need to compute the sum failed

  181. Jessie Stanley says:

    Hi Svetlana,

    I hope you may be able to help?1?

    I am designing an attendance spreadsheet for a school using a set of alphabetic codes for attendance, for example - P- Attended, L - Half day, A - Absent.

    I also have a summary box for reporting purposes and need to count attendance and absence based on gender (M/F) and grade(k - 10) and ethic background (I,NI) (a column for each).

    Therefore I need to count the number of Ps, or A's that exists in the attendance data in the row IF gender=M and grade=K and ethnicity=I

    I also want a value returned if the cell is still empty.

    I am have tried using : =COUNT(IF((C6:C19="K")*(E6:E19="M"),F6:BM19="P"))
    But this returns will a nil value.

    Let me know if I need to send through the spreadsheet.

    Thanks in anticipation.

    Warm regards,

  182. sue says:

    I'm trying to get excel to find numbers ending in a 7 (i.e. 47, 57, 67) and then need to tell the computer is raise the number to 47 to 50, 57 to 60 and 67 to 70. Please can someone help please?



  183. Artūrs says:

    =IF(C7>0;"OK";"BAD") this one is working, but if I put region
    =IF(C7:G7>0;"OK";"BAD")or =IF((C7:G7)>0;"OK";"BAD") excel give me VALUE

  184. Artūrs says:

    Hi! For my previous question I get answer! But I have other one - if I want to count only "OK" results for IF function for example H7:H19, what function do I need to use?

  185. Shannon says:

    I'm trying to calculate the total number of individuals who meet a certain criteria, but I can't seem to make the "COUNTIFS" formula fit what need. I have a column, EDU and here is some sample data. I want all data to count as 1 except "HS" which should not be counted. The purpose is to calculate the number of individuals who have post-HS education.

    Do you have suggestions on how to make the formula work with multiple conditions?


  186. Ruman D says:

    Hello, i thank you for helping others unconditionally. I have asked for help and posted twice. Number 177 is the only one i can find. I understand that you have extremely high traffic volumne. But i was just wondering if i am able to get help here or even my request for help is misssed. thank you again for the good works you are doing for all persons and society.

  187. Sam Powell says:

    I've been reading around and I apologize if I've missed this(the information has me a bit...confused and I overwhelmed)

    I am trying to keep track of some projects between me and a person.

    Under one column of cells I have the type of project and in another I have who did it.

    I have somewhere a list of how many of each TYPE of project is done and beside that who it was done by so I can keep track of the giant project list.

    So say...the C columne says "Project type 1" or "Project type 2" and the D column has "Done by Susy" or "Done by Dave"

    I was keeping track of "Project type 1" in one cell. So it's counting how many we did of that type very well.
    Then I did the same for Project type 2.

    But when I got to the point of: "Project type 1 done by Susy", I tried to put in a formula to count how many times Project1 was done by Susy and another counting how many times it was done by Dave.

    ...cept it doesnt work. And I'm not quite sure how to do it. I got confused reading the information because it seems to always be consistent info in different columns or different info in the same column and rarely did I find "Only count if both these conditions are met"

    Is that..even possible? Neither have value numbers. They use words rather than numbers(obviously they arent actually called 'Project 1' and '2'.

    Gah..just writing gets me confused.

  188. Anonymous says:

    2 RICKY DXB MALE SHJ Formula ? Formula ? Formula ? 7
    3CHRISTINA SHJ FEMALE DXB Formula ? Formula ? Formula ? 2
    4 LIBA SHJ KIDS AUH Formula ? Formula ? Formula ? 2

  189. Eddie says:

    Thanks Svetlana, you just saved my life and a lot of typing :) awesome post

  190. Vishal says:

    Hi,I Am making a file for my poject.I am facing the some problem in xls pls do help in that. I have to add the farmula that How may rows is on Time and Early only for NAGAR as given the example. total should be come in one cell for both On time+Early of the NAGAR name.

    NAGAR On Time
    RPRAD Early
    BBIAD Late
    CCUBG Late
    NAGAR On Time
    RPRAD Late
    BBIAD Late
    CCUBG Late
    NAGAR Early
    RPRAD Early
    BBIAD Early
    CCUBG Early
    NAGAR On Time
    RPRAD Early
    BBIAD Late
    CCUBG Late
    NAGAR Early
    RPRAD On Time
    BBIAD Late
    CCUBG Late
    NAGAR On Time
    RPRAD Late
    BBIAD Late
    CCUBG Late
    Thank you.

  191. LG Singh says:

    How to count the number of values in a cells separated with a coma.
    The number of values should be 4 in the 3 cells. Count function only counts 2 values.

    • Alexander says:

      Please use this array formula (press Ctrl+Shift+Enter to complete it):

      Where A2:A10 is your cells with data. Please note that the formula will return wrong results, if your cells contain some text, e.g. "1,text,text20"

  192. Dee says:

    How do you count two different 'tasks' that are in the same column but only if they are marked 'active' in another column.

    I seem to have botched this up & have ended up with this mess:

    =COUNT((IF('Combined Project & Milestone'!F:G,"DESTRUCTIVE TESTING")),+OR((COUNTIF('Combined Project & Milestone'!F:G,"INITIAL REPORT")),+(COUNTIF('Combined Project & Milestone'!F:G,"Active"))))

    REALLY hope you can help soon.


  193. LG Singh says:

    Thanks Alexander, but the formula counts empty cells also in the column. How to count only the cells with values, neglecting empty cells?

  194. LG Singh says:

    Thanks again Alexander.

  195. Pushap says:

    I am ubable to count below data

    Name Time
    A 12:03:00 PM
    B 12:17:00 PM
    C 12:00:00 PM
    D 12:44:00 PM
    E 5:01:00 PM
    f 12:21:00 PM
    A 12:03:00 PM
    B 12:17:00 PM
    C 12:00:00 PM
    D 12:44:00 PM
    E 5:01:00 PM
    f 12:21:00 PM
    A 12:03:00 PM
    B 12:17:00 PM
    C 12:00:00 PM
    D 12:44:00 PM
    E 5:01:00 PM
    f 12:21:00 PM

    NAME Count

    Please help

  196. Lotus says:

    Hello there, I hope you can help with the following worksheet with multiple tabs.I Maybe COUNTIF could work but i really can't figure it out how.

    Code Account Title Jan-05 Feb-05

    1000 Advertising $750,75 $-

    2000 Office Equipment $- $-

    3000 Printers $- $-

    1st- I would like to set the date in each column as 16Jan-15Feb, 16Feb-15Mar etc., so that it sums up the invoices amount for each code of only those dates, at the moment it only adds each full month, and i can't seem to find a way to change it without errors. This is how the formula is shown:

    =SUMIF('Itemized Expenses'!$J:$J;"="&($A6&TEXT(C$4;"mmm-yy"));'Itemized Expenses'!$E:$E

    2nd - I would like to add another formula to this, which also adds the amounts within those dates depending on methods of payment and display it in another column, that is the total paid in cash, total credit card, total direct debit, etc.
    Thank you very much in advance for your help.

  197. Ravi says:

    I have two columns, column 1- Dates, column 2- Names.
    01/02/2015 ARYA
    01/02/2015 ARYA
    01/02/2015 ARYA
    02/02/2015 ARYA
    From the above example, I want the count to reflect 2, in other words I would want it to count as a single occurrence per day i.e., 01/02 - one occurrence & 02/02 one occurrence. Please help me with this. Please help me without date ranges as i need to use it for the entire year for different names.

  198. Roshin Stephen says:

    Hi Alexander,

    I guess even my query has been missed out. 176 is my number. So please if you can help me out.

    Thank you.

  199. H says:

    Excellent! Thanks for the clear explanation. This really helped.

  200. Lea says:


    How can I use countifs and vlookup together?

    Thank you.

  201. Randhir Godase says:

    Names Sales Profit
    Kim 100 10
    John 250 25
    Tim 300 30
    Tom 325 33
    Sheetal 350 35
    Nidhi 375 38
    Shyam 400 40
    Rahim 425 43
    Ali 375 38
    Sheetal 325 33
    Tom 275 28
    Seema 225 23
    Tom 175 18
    Kim 150 15
    Sheetal 200 20
    John 250 25
    Tim 300 30

    How use 'countif'formula for
    Q : Excluding Kim there are how many people in the list?

  202. Mike says:

    ok i am trying to make spread sheet that tracks the units average score for said events, but I don't wont to enclude the certain personal that are on a profile preventing them to take a certain event.

    So I guess the question is there a way to put an AVERAGE Formula and a COUNTIF Formula in the smae cell, in order track all this type of information or am I too far out in Left Field this function is impossible?

    Thanks for your time and answer/assisstance with this issue I have.

  203. Anonymous says:

    Recipt Credit
    Cash Book Bank

    564 914308
    30,000 30000
    30000 4654
    43660 30000
    1245247 19344
    30000 465654

  204. Raj Jagtap says:

    Recipt Credit
    Cash Book Bank

    564 914308
    30,000 30000
    30000 4654
    43660 30000
    1245247 19344
    30000 465654

    I want to know matching values in col a & b
    e.g there are 2 values (30000) in col b
    it should show corresponding 2 values in col A & not 3
    values as appeared in col a

  205. A. Clemente says:

    Hello Svetlana, I really hope there is an answer to this I've tried several ways but it doesn't seem to work. Assuming I have data in A1:A3 and the data totals are in A4 & A5. The data that needs to be added across for example are 2/2, 3/2, 4/1; the left number is the attendance for adults and right side are the children. The data total for A4 would = 9 and A5 = 5. Does it matter if the data in one cell is seperated by either symbol? Ex. 2/2 or 2:2 or 2+2? I need HELP how to add the data across but keeping left side total from right side. Is there a simple solution?

  206. TechTeacher says:

    Hi. I have an excel spreadsheet with first names in column A and last names in column B and other information in columns C and on. I access and change this file daily. I need to know when a specific person shows up 6 or more times and then I have to move the data to another sheet. I already have it sorted by last name, column B, but I still have to go through and count each individual name to make sure there are 5 or less or I have to move them. Is there a way to use COUNTIF(S) to let me know if there are names that appear more than 5 times and maybe even what the names are? I have to go through this process every day and it would save me a lot of time looking through the 800 rows.

  207. Santosh says:


    A B E F
    1 Santosh 10 Raj
    2 Raj 5 Sajan
    3 Sajan 8 Ankit
    4 Ranjan 9 Tara
    5 Sameer 10
    6 Priyanka 4
    7 Ankit 8
    8 Tara 6

    in the above spread sheet there is some names from A1 to A8 consequent the figures on B1 to B8,

    My question is Cell E1,2,3,4 i mentioned few names in jumbling way ok, if those names are in the list of A1 to A8 then i want the consequent figure (which mentioned at B1 to B8) to be visible @ F, ok

    guide me with the formula...............

    • Jadon says:

      Here you go...

      if you want name with consequent figure..

      If you only want the figure then...

  208. Felipe says:

    Greetings from South Africa!

    I am creating a database to count four different types of clinical personnel receiving training on medical male circumcision. Information will produce two basic spreadsheets. One counts the number of trainees per training type (8) and segregating each clinical personnel type per month (1,350 cells). The other segregates each clinical personnel type and training received per province (state) (9,350 cells) Thanks to information from your website I created formulas that are working, but my question is how can I copy and paste the formulas that automatically change the month I want to capture. An example is below where 01 represents January. How can I copy and paste the formula setting the month to change automatically to 02, 03, etc?

    Thousands of Thank yous if you can help.

  209. saista says:

    i have a problem
    that is i have a range of data from A2:A308 i want to place a criteria that if the data in the range falls =8 and =13 it should display tertiary

  210. zeta says:

    i have a range of data from A2:A308 i want to place a criteria that if the data in the range falls less than 8 it should display primary if greater than equal to 8 and less than 13 it should display secondary and if greater than equal to 13 it should display tertiary

  211. Jaaaaaaaaaaaaason says:

    Part code Qty
    1 12 Part number '1' total-
    2 85 Part number '2' total-
    1 47 Part number '3' total-
    4 69 Part number '4' total-
    3 32
    1 12
    4 45
    2 12
    3 36
    1 58
    4 51
    2 78

    Please help in summing the total of each part number...

  212. Rebecca says:

    Hi there. I need help with Countif. So I have two columns, both are dates.

    1. Target Award Date
    2. Actual Award Date

    I need to count how many actual award dates were before the target.

    For example.
    Target Award Date is 2/10/2015
    Actual Award Date is 2/8/2015
    This should be counted

    If the Target Award Date is 2/11/15 and actual award Date is 2/15/2015, then it wouldnt be counted.

  213. John says:


    I have two sheets. One is called "Rollup" and the other is called "Data". On the "Rollup" sheet, I am using column "E" for my formula and what I'm trying to do is count anytime whenever the name in column "B" of the "Rollup" sheet appears in D2:D39228 of the "Data" sheet. So, on the "Rollup" sheet for example, cell E2 will have a COUNTIF formula that asks how many times the name in B2 of "Rollup" sheet appears in D2:D39228 of the "Data" sheet. Does that make sense?

  214. MarcusM says:


    I have 3 columns containing the following info regarding subscription period to a website: User, Service Start Date, and Service End Date, with the dates ranging from 2013 to 2016. I'm having some trouble using both COUNTIF and COUNTIFS to get how many Users (A2:A617) are active on a particular period (e.g. Jan-2013 has 21 active subscribers, Feb-2013 has 24, etc.).
    I would really appreciate your help.
    Thank you.

  215. Ana says:

    I have two columns I have to match. I thouught I can use COUNTIF function to calculate the frequencies from column B matching it to a value in column C (as there are more then one values in column C, so I have to choose to calculate the frequencies of each of them separately). How do I do that? I tried all the combinations but I don't think I understood the above explaination correctly.

  216. William says:

    Long story short, thank you, Svetlana, for this post! Your explanation and examples using COUNTIFS were just what the doctor ordered!!

  217. Leela says:

    Hello Svetlana,

    I'm not sure if I can use COUNTIF/COUNTIFS for my following query. Perhaps the solution may be SUMPRODUCT as I have tried. I'll wait for your advice.

    I have a column (A) going down 1000 rows. Each cell has either "Inside" or "Outside" typed in them. The next column (B) has dates in each cell (also going down 1000 rows). I need to search Column A and if the cells contain the word "Inside" or the word "Outside", total the number of dates in Column B that are less than or equal to(<=) Today(). There will occaisionally be blank cells in Column B. I have come up with this formula but it still counts the blank cells in Column B :-


    Greatly appreciate any help.


  218. Leela says:

    Hello again Svetlana,

    Apologies. I neglected to type the words "Inside" and "Outside" in full within the formula in my last post. Sorry!


  219. Ghazi says:

    hi, i need your help:
    can i incloCan I include function within the COUNTIFS function like the following example:
    It does not give me correct answer. Please help me.

    thank you.

  220. Ghazi says:

    I'll explain what I have:
    I created color function to count cells with certain color and now I want to count celles that has value and certain color at the same time

  221. Jake says:

    Hi There

    I was wondering if it was possible to have 3 COUNTIFS formulas in one cell?

  222. kardon says:

    Hi There,

    I am not able to find the right countif formula to count "AA" in next column with reference to another column!! For example in Column 1 I have different Cable types and in next four columns whether is applicable or not I had mentioned with AA, BB, CC, DD. Here I have to count how many "AA" are available against one type of Cable. Could any one please help me out??

    • Hi Kardon,

      If column 1 (let's say column A) contains unique cable types only, i.e. a single record for a certain type, you can use the following formula:
      =COUNTIF(B2:F2, "AA")

      Where columns B - F contain AA, BB, CC, DD. The formula counts the number of AA in each row.

      If you have multiple instances of the same cable type in column A, then the best solution I can come up with is this lengthy formula:

      =COUNTIFS(A2:A8, "cable1",B2:B8, "AA") + COUNTIFS(A2:A8, "cable1",C2:C8, "AA") + COUNTIFS(A2:A8, "cable1",D2:D8, "AA") + COUNTIFS(A2:A8, "cable1",E2:E8, "AA") + COUNTIFS(A2:A8, "cable1",F2:F8, "AA")

      Where "cable1" in the cable type, 2 is the first and 8 is the last row with data.

  223. Steven says:

    Is there a way to combine a COUNTIF with a MIN function? I have three different amounts for each client, and I need a count for how many times the first value was the least of the 3, how many times the second value was the least of the 3, and how many times the third value was the least of the 3. Any help is greatly appreciated.

  224. MANSUR says:

    i have a sheet in which a column shows percentage of attendance i have to provide the formula for next column to give the values for the percentage..for example
    if the student have percentage in between 75 to 80 i have to give 01 marks and 81 to 85 then 02 marks ,86 to 90 03 marks etc.the how to write formula?

  225. MANSUR says:


  226. Henry says:

    Great article; maybe you can help me. I have a named table with over two dozen date columns and one column of text.

    I want to count the number of dates within a date range and that have a specific text value

    date1 date2 ... date26 Text

    Logically this would be:

    If Text = "Major" and Date = upperLimitDate then count it.

    I can't get COUNTIFS to do this.

  227. Henry says:

    My example was cut off, let me try again...

    If Text = "Major"
    and Date GE lowerLimitDate
    and Date LE upperLimitDate then count it.


    • Hi Henry,

      You can use a formula similar to this:

      =COUNTIFS(Table1[text], "Major", Table1[date], ">4/10/2015", Table1[date], "<4/20/2015")

      Where [text] is your Text column, [date] - column with dates, 4/10/2015 is lowerLimitDate and 4/20/2015 is upperLimitDate.

  228. Thomas says:

    Evening Svetlana,
    I have used your posts many a time to gain answers for my questions and until now was always able to find an answer. I have an issue that I cannot figure out. What I am looking for is a way to count the number of records that meet multiple criteria. My issues comes when adding in the multiple fields. I have a project list with Start dates, Team name, Department and Type of project. Above that I have a list of the start and stops for each week of the year.
    What I am trying to do is count the records where the start of the project is between the start\stop date for the given weeks but only when the project team is a given name (Blue, or Green) or the Department is a given name (SRT). The fields are exclusive but complimentary so I need to add an "or" statement but cannot get it to work.

    • Hi Thomas,

      To count with the OR logic, you need to add several SUMIF or SUMIFS functions, for example:

      =COUNTIFS(A2:A10, ">=start date", A2:A10, "<=end date", B2:B10, "team1",) + =COUNTIFS(A2:A10, ">=start date", A2:A10, "<=end date", B2:B10, "team2",)...

  229. Henry says:

    found the answer. I need to test criteria on each and sum:

  230. Nahla says:


    i want to count if the column contains a date(unconditioned)

  231. Yogesh says:

    Excellent information dear...!
    This information made my day.
    Once again, thanks a lot.

  232. sharon says:

    I have a problem with a "countifs" statement and I was wondering if anyone can help.

    One of my columns has cells where the content is <=5 as the value and it isn't a calculation. I want my countif to count how many instances of those, but when I put it in my countif calculation it sees the <= as part of the calculation and produces the wrong results. Is there any way to see <=5 as a value instead of a calc in that cell?

    Any help is appreciated.


    • Hi Sharon,

      I cannot figure out a way with COUNTIFS either. But you can use the following array formula instead:

      Please remember to press Ctrl+Shift+Enter to enter it correctly.

  233. Rachel says:

    Hi, I'm trying to calculate the following.

    IF a cell in B2:B19 contains the word "Food" THAN I want the numbers in corresponding cells in E2:E19 to be added up.

    For example B2 and B5 contain the word "Food". Their corresponding cells E2 and E5 contain "E2: 8 and E5: 10", the total of the formula would be 18.

    The B column contains various Types in text and the E column contains different amounts of that types in numbers. I want to see the totals of every type without having to manually choose E2+E5 etc.

    I hope this makes sense.

  234. paula says:

    hi! I have a column 3400 reference value (persons id) and want to count how many times listed in an other column (trips in taxi). can you help me? thanks

    • Hi Paula,

      Amusing that your persons id column contains unique values only, you can use a formula similar to this:

      =COUNTIF($B$2:$B$1000, $A2)

      Where A is the id column (row 2 is the first row with data) and B is the other column (trips in taxi).

  235. Joe says:

    Thanks worked great for me!!

  236. Mark says:

    I'm trying to use a COUNTIF formula, where the criteria is a cell reference to a formula result.

    Is there anyway the COUNTIF formula will pick up the formula result value ?

    A,B, C

    Result in "C1" will be "8"

    Now I would like to use value "8" as "criteria" in the COUNTIF function.


    But it won't pick up the "8" value but returns "0"

    Thanks in advance...

    • Hi Mark,

      When you enclose cell references in quotes, Excel interprets them as text strings. Once you remove "", your formula will work fine:
      =COUNTIF($AA$1:$AA$100, $C$1)

      You can also put it like =COUNTIF($A$1:A$100, "="&C1)

  237. Pam says:

    1st 2nd 3rd 6th 7th 8th
    7:59am None
    Scheduled Closed 8:02am No Show 8:03am
    I need a formula to only count the times which are equal to or greater than 8:00am. i need it to only count the times.

  238. marlon says:

    Box ID Sku Sku Sku Sku Locations
    A 123 123 456 123 Shop
    b 123 456 789 123 dock
    C 789 456 789 456 whse
    D 123 123 store1

    Search sku 123 box ID locations records
    a shop 3
    b dock 2
    d store1 2

  239. marlon says:

    Base on the data given, how can I get the desired output?
    Box ID Sku Sku Sku Sku Locations
    A 123 123 456 123 Shop
    b 123 456 789 123 dock
    C 789 456 789 456 whse
    D 123 123 store1
    Needed result/output:
    Search sku 123 box ID locations records
    a shop 3
    b dock 2
    d store1 2

  240. Chad says:

    Average from a sum of cells.
    Column A Column B
    425 Current
    365 Current

    354 Not Current
    142 Not Current

    454 Current

    Average total of only the ones that state Current, leaving out the Not Current and blanks.

  241. Chad says:

    Oops wrote it backwards.


  242. David says:


    I have 2 columns of data:

    1st column:

    2nd column:

    I want to count how many Seniors have a figure < 60 in column 2.

    The answer should be 2 - how can I do this please?

  243. Runu says:

    If i want to count "P" , "Late" and "Half Day" in same cell what will be the formula using countif formula.

  244. David Betts says:

    Thanks Svetlana,

    and if I wanted to use a cell reference instead of 60 do I just use "<C1" for instance?

  245. Karim says:

    Hello Svetlana,
    I have a list of parts with different end connections, some of those parts have more than one connection:
    0.5" NPT 1" NPT 0.25" NPT 0.75" NPT
    A X
    B X X X
    C X X X
    D X X X
    E X X X
    F X X X
    Can i use the CountIF function to tell which parts have the most common connections?

  246. David Betts says:

    Many thanks Svetlana!

  247. Ritchie says:

    Hi, I keep getting a value error for the following formula. Column E contains data from a validation list so I am not sure if this is affecting the outcome. Column K has words written as RED, GREEN, AMBER. Essentially I want to know how may meet the criteria of both Live and Red.

    The validation data is held in a worksheet entitled Sheet 3 - cell range D5-D9.

    =COUNTIFS('Programme Risks'!E4:E944,"Live",'Programme Risks'!K4:K920,"RED")

    Thanks in advance.

  248. Mihai says:

    I have 2 columns with multiple values. I want to count the unique No.of customer codes for each sales Agent. For instance, the value for Agent X shall be 2 (10 and 6).

    Sales Agent Customer code
    X 10
    X 10
    Y 25
    Y 5
    X 6
    Z 10

  249. Hassie says:


    I am trying to count the number of times multiple values appear in a cell across a row. e.g
    A1- 4,5,8
    B2 - 5, 31

    This would add up to 5, but i can't find a formula that can calculate this.

    Thanks in advance

  250. Loy says:

    09/01/2015 07/01/2015 LATE
    04/01/2015 31/01/2014 ONTIME
    10/01/2015 06/01/2015 LATE
    10/01/2015 LATE

    I would like to ask what will be the formula if I want to have the status as "Late" or "On-time" for the data table above. It will also show a "Late" status if it already lapsed the expected date even if in the "Actual Delivery" column is blank. It will also not categorize the status if the expected delivery column is blank once it is not yet past the due date.

    Thanks in advance.

    • Hi Loy,

      You can achieve the result by using nested IF functions.

      To be able to suggest a proper formula, I need to understand the conditions better.

      So, assuming that "Expected Delivery" is column A and "Actual Delivery" is column B, the formula returns:

      "Late" if A2 is not blank and B2>A2, or if B2 is blank and A2 is greater than today;

      "On-time" if both A2 and B2 have dates and B2<=A2;

      No status (empty string) if A2 is blank "once it is not yet past the due date". How do we know this? What is the due date?

      The following formula works with the above logic, except that it does not check "once it is not yet past the due date":

      =IF(OR(AND(B2>A2, A2<>""), AND(B2="", A2>TODAY())), "Late", IF(AND(A2>=B2, A2<>"", B2<>""), "on-time", ""))

  251. Stevie says:

    I need to count multiple criteria. I have a spreadsheet where the counts will be housed. This spreadsheet is looking at another spreadsheet's data to obtain the counts. In column A the city will be located. There are 7 different cities in a column. In column B there will be the project names. There will be about 4 of these. Column C has the start date and Column D has the end date. I want my formula to look at column A and find Sacramento, then look at column B for only specific project name, then look at column D for the date range of 3/26/2015 to 4/25/2015 and provide a total count.

    So for instance, Sacramento will have 4 projects listed so there will be 4 separate rows for Sacramento. One row for each project. But I only want to count two of the projects. How would I put this into a formula?

    So if the project names are Red, White, Blue, and Yellow (so 4 rows for Sacramento). I want my formula to look at column A for Sacramento and count only if column B has Red and White and column D for the date range of 3/26/2015 to 4/25/2015.

    Right now I have a formula that counts how many cells are within that date range for Sacramento but it is counting for all 4 projects. I want it to only include the date range for projects Red and White (column B).

    Thank you for your time and effort.

  252. Tom says:

    Dear Svetlana,

    I need to create a countif formula that will allow me to start counting in multiple spots.

    I.E 0 - 0 - 0 - 0 - 100 - 100 - 0 - 200 I want it to count FOUR becuase the trend starts. I need this for multiple lines with different starting points.

    You can treat this as says and months it was a new product so we didnt have sales in the 1st 4 months, but sales started on the 5th month and we had two months with 100 cases the 7th month we didn't have any sales and the 8th month we had 200cs. - I.E we averaged 100 cases per month. 400 cases in four months.

    the next item may be

    I.E 0 - 0 - 50 - 0 - 100 - 100 - 0 - 200

    Please help. I have tried everything i can think of.

    • Alexander says:

      Hello Tom,

      If my understanding is correct, you need a Helper Column to fulfill this task. For example, if your data are in columns A:H, then in cell I2 you need to place the following array formula (use CTRL+SHIFT+ENTER to enter it):


      Where A2:H2 are the cells with data. You will get the number of months from the sales start (4, 6, etc.).

      Then copy the formula down across the column I.

      After that you can calculate Average in the adjacent column using this formula:

  253. Anmol Singh says:


    I have a bill wise sale data in which i want to count how many times a category is coming.

    Setting is as follows:
    Col A: Bill number (27000 + rows including duplicates, and 2000 + rows unique)
    Col C: Time period of sale (4 time periods)
    Col D: Product code/name (multiple)
    Col H: Category (18 categories)

    Objective is to find non duplicate unique bill count (Col A), for all 18 categories (Col

    H) set in rows, and 4 time periods set in columns

    Pls. help with a formula


  254. Chris says:

    Ms. Cheusheva

    I have a good one for you, I need to take and if column B has the word ALSE I need to count the number in column C and post the resulting total number. Any Ideas?

    Thanks for all the help

  255. Brian says:

    Looking for a formula that will add cells if another cell matches a specific value.

    A1=1, B1=2, C1=8, D1=60
    A2=2, B2=4, C2=4, D2=80
    A3=1, B3=1, C3=12, D3=60
    A4=3, B4=2, C4=4, D4=120
    A5=3, B5=1, C5=4, D5=40
    A6=2, B6=2, C6=8, D6=80

    What I'm looking for, is B7= the sum of the values for the B column that have a 1 in it's A cell for that row. B8= the sum of the values for the B column that have a 2 in it's A cell for that row, and so forth. This is sort of what I have been looking at, but have not been able to complete it. IF(A1:A6=1,B1+B3,??

    Here is an example file: https://www.dropbox.com/s/3tbit1g63hjohw5/Book1.xlsx?dl=0

    Thank you for any help.

  256. jana says:


    I want to maintain my attendance register with conditions. Like if a employee took more than 4 days leave (continuously). automatically it should display straight to his name: warning

    for example

    Attendance Register
    employee 1 : b1:af1 (31 columns for month)

    If he took 4 days leave continuously then only result should show warning message.
    If he took 1 day leave and works for 2 days and again took leave. no need to warn.

    I just need if he took continuously 4 leave days with b1:af1, result warning !

    Please help, Thanks in advance for responding to my queries

  257. Lexi says:


    Sort of complicated question. I have 3 columns that I am working with. All three columns are filled with "Y" or "N" telling me a yes or no answer.

    I want to count if there is a "Y" in column B AND a "N" in column D for ONE single row and then add all those together.

    This is what I have but it is not calculating it correctly, can you help?
    =COUNTIFS(B2:B39, "Y", D2:D39, "N")

  258. Nurse Betty says:

    Hello there!
    I am working on a tracking worksheet where I would like to create a formula in sheet #2 to be able to calculate a total count of a specific LETTER CODE on a SPECIFIC DATE. The data would be in Sheet #1.
    Sheet # 1 has the following:
    Column A - dates(Example- 5/1/15, 5/2/15,etc)
    Column B - Letter Codes (Example - C, AD,L,K)

    Then I have Sheet #2. Here is where I would like a formula to show the total count of a specific LETTER CODE on a SPECIFIC DATE from sheet 1.
    Any help or guidance is EXTREMELY appreciated!!!
    Thanks in advance!!

  259. Gavin says:

    A B C D E F G H
    3000037398 0040 4
    3000022099 0410 3.6 7.6 7.6
    3000022099 0410 2.4 2.4 3 3
    3000037398 0040 5
    3000037398 0040 2.6
    3000037398 0040 2.4
    3000022099 0410 1.4 2.5

    I would like to lookup the value / item in Column A which may appear more than once and then total up all the numbers from columns "B" to "H" relevant to the value in "A"
    i.e the total for number 3000037398 0040 will be 14.


  260. jana says:

    I want to maintain my attendance register with conditions. Like if a employee took more than 4 days leave (continuously). automatically it should display straight to his name: warning

    for example

    Attendance Register
    employee 1 : b1:af1 (31 columns for month)

    If he took 4 days leave continuously then only result should show warning message.
    If he took 1 day leave and works for 2 days and again took leave. no need to warn.

    I just need if he took continuously 4 leave days with b1:af1, result warning !

    Please help, Thanks in advance for responding to my queries

  261. jana says:

    Please help me

    I want to maintain my attendance register with conditions. Like if a employee took more than 4 days leave (continuously). automatically it should display straight to his name: warning

    for example

    Attendance Register
    employee 1 : b1:af1 (31 columns for month)

    If he took 4 days leave continuously then only result should show warning message.
    If he took 1 day leave and works for 2 days and again took leave. no need to warn.

    I just need if he took continuously 4 leave days with b1:af1, result warning !

    Please help, Thanks in advance for responding to my queries

  262. Kel says:

    Hi Svetlana, I would really appreciate any help you can give me with the following:

    I have 2 columns of data like this
    Column 1 Column 2
    Beer 1
    Wine 2
    Beer 1
    Beer 2
    Wine 3

    and so on,is there a way to calculate the data to get the total amount from column 2 per item from column 1?

    Thanks in advance,

  263. Sarah says:


    Thank you for your helpful tips however I have a scenario non-applicable to the ones you have provided. I'd greatly appreciate if you could help me create a formula for the following scenario:

    I need to get a count of Employee Names that do not have an assignment to an Employee Group. For Example:

    Column A
    Employee Names

    Column B
    Employee Group

    I need a count of cells to be counted only when there is text in Column A and no text in Column B.

    Currently I am using the following syntax: =Countifs(A2:A1000,"*",B2:B1000,"")

    This syntax is returning a sum of cells with formulas minus those cells with data populated.

    How can I fix this?

  264. kerem says:

    hi i want to use cell as a criteria by counti formula,
    and i could not find out solution. is it possible?
    if possible how i can do it

    • Hi Kerem,

      Of course, you can use a cell reference as a criteria. In this case, you enclose the operator in quotation marks and add an ampersand (&) before the cell. For example:

  265. mangi says:

    Thanks Svetlana for helping me and resolve all my statistic issues.

  266. Jose Marcos says:

    Hi Svetlana,

    I am working on date range and I want to get the weekly counts with inclusive dates. Can you pls. help me how to figure out the cell reference with variable dates?

    Thanks in advance,

  267. Sourav says:

    Time Criteria using counti if

    I want to know how many people arrived before 10 AM from that column.

    Excel is not giving the output.

    Would b great if anyone could help

  268. idrish khan says:

    i want to know how can i count in given below table there is final result is latest revision.

    No. Rev.00 Rev.01 Rev.02

    1 C D B
    2 C B B
    3 D C B
    4 D B A

  269. Ken says:


    I wanted to know how to write a formula in Excel for the following:

    Suppose there are variable names available in individual cells in a column in one sheet. Excel needs to count (and subsequently sum up) the number of times those names appear in a column in another sheet and display the answer in a cell in another sheet.

    Greatly appreciate your assistance on this.


    • Hi Ken,

      Your Countif formula may look similar to this:
      =COUNTIF(Sheet2!A2:A100, Sheet1!A2)

      Where column A in sheet2 is where all the different names appear, and A2 in sheet1 is an individual cell with a variable name you want to count.

  270. Chris says:


    I have a table in excel with dates, times and list of random names for each day at different times.
    I would like to be able to count how many times all the different names appear. It is very much like an attendance sheet (only I'm trying to count non attendances), with session starting every 30 minutes. Ideally, I would also like to know times and dates with most non attendances.

    Thank you very much for your help.

  271. kerem says:

    thanks a lot Svetlana, it is really very helpful

  272. Mark says:

    Hi Svetlana.

    I have a table and the rows contain either "TRUE" or "FALSE" in each cell.

    Is it possible to count how many times have "True" happened 3 in a row on each roll? e.g TRUE TRUE TRUE

    So at the following example, "True True True" happens 1 time only:


    and what if I have 4 True happened in a row:


    Will Excel read "True True True True" as 2 times?

    Thank you!

  273. meena says:

    Hi Svetlana,
    Hoping you can help with my data comparison.
    I have two sets of timesheet data (3 cols - project, person and hours) and i have managed to find ones where all three match between the sets. what i am trying to find out is where the Project and person match but the hours have changed... i want to find out by how much the hours have changed

    So if in Set A - person x has done y hrs against Project 1
    But in Set B - Person x has done Z hrs against project 1.

    i would like the formula to tell me that Person X's hours against project 1 has changed by (y-z)hrs

    thank you ... this would save me hours of manual work!


  274. anish says:

    How do i count a row based on specific column data. Like count the number of cells with numbers and exclude Fridays from the column.

    well simple way is this =COUNT(E22:AI22)-COUNT(I22,P22,W22,AD22)

    but i want it more automated where the COUNT(I22,P22,W22,AD22)are the Fridays and COUNT(E22:AI22) is the cells with numbers

  275. Melissah says:

    Hi, I'm trying to count the number of items between two dates which meet a criteria (A3). So far i have got this, which seems to be only giving the results which are greater than 1 May as opposed to between 1 May and 31 May:


  276. verymo says:

    How to Solve this.
    In cell B5, use the COUNT function to calculate the total number of students in the class.
    where A17:A52 contains student IDs.e.g,393-411-8,393-440-4

    you dont have to ise COUNTA,COUNTIF,COUNTBLANK etc functions.only use COUNT function

  277. lakshmi kanth says:

    I want a formula that counts 2 or more rows like AND gate logic.
    one row is having "pens" and another row having "RED color".
    I want how many red color pens are there.

    Thanks in advvance for ur assistance

  278. Murugesh says:


    15-Jun MDM
    16-Jun PTP
    17-Jun DTDE
    18-Jun MDM
    19-Jun PTP
    20-Jun QTC
    15-Jun RTR

    Above is the one which is there in my excel.

    Am using formula as to find between dates from 15th to 19th


    Along with this, I would like to add condition which Matches I column too, say if I need to see between the date rang what are all there as "MDM", in this example it should return a count as 2.

    Kindly let me now how to add the condition.

    • KuRo says:

      hi Murugesh, you might want to try this. was having the same problem but manage to solve it like this. reason for using DATE() was because, they are comparing date serial with text before. but since you converted your dateText to the same comparing format, it would works. hope it works for you too! :)


  279. chiran says:

    No accident type A B C R
    1 A Total-... ... ... ...
    2 B
    3 B
    4 R
    5 B
    6 A
    7 C
    accident type can filter.I want to get the total for each accident type for long data sheet. Thanx a lot for this service

  280. Ben says:

    I’m trying to figure out how (or) if I can apply countifs to my spreadsheet. What I have going:
    In Column “I” I have Panel ID i.e. C3D
    In Column “J” I have # of panels (“1” per line item currently)
    Column “L” I have panel ID again
    And “M” I have QTY
    My thought process behind this so far, is when I receive a trucking invoice, I receive, i.e. 20 panels of C3D. Well, currently I have to enter C3D 20 times with a “1” next to each in # of panels to get the QTY in “M” to count it. What I want to do here is be able to enter “20” in # of panels in “J” for ID C3D in “I” and have it say, on row 9 in column J is C3D, and for that entry is 20 of them and count 20 for C3D. Say tomorrow I can 3 more C3D. Next row in row 10, C3D with 3 and the countifs not stop at the first one with just 20, but keep going and say, hey, 3 more for a total of 23.
    Any help would be greatly appreciated!

  281. Tanya says:

    Good Morning!
    This tutorial was very helpful. I am having an issue with some dates in the formula:
    =COUNTIFS(tblEvents[Date of Request],">=6/15/2015",tblEvents[Date of Request],"=J1",tblEvents[Date of Request],"<=J2")

    Thank you! Tanya

  282. Larry says:


    I am trying to count how many consecutive days each employee works in a given date range. Here is how my spreadsheet is set up:

    A1 = Title Name
    A2 to A100 = Employee Names (an employee name may be lkisted 20 or more times in a row and then the next employee's name - in alphabetical order)

    C1 = Date Worked Title
    C2 to C100 = Dates (consecutive dates)

    H1 = Hours Worked Title
    H2 to H100 = Hours worked each day

    So, I am trying to find for each employee, what is the maximum days in a row (consecutive days) each employee worked.

    Thanks in advance.

  283. Chris says:

    I have a formula that is working to get my answer, but the problem is I need to use that answer in another formula and that isn't working. Any help on that?

    1st Formula: =IF(COUNTIF(F:F,"*Corr*"),"1",)

    2nd Formula that need to be able to count the "1" in the first formula. : =SUMPRODUCT(--('Raw Data'!C:C=C4),--('Raw Data'!H:H=1))

  284. Jim says:

    Having a problem figuring out why a countifs formula doesn't work. I have a file that has several types of status closings in it - Verified; Verified-Paid; Verified Denied; Canceled; Taken; Assigned.

    Based on the client I want to count the number of any trip status that is {Verified* or Taken* or Assigned*}

    And further has the Level of Service that is any of . . .

    =SUM(COUNTIFS(Table1[Broker Client],"VA Client Health",Table1[Status],{"verifi*","Taken*","assign*"},Table1[Trip Level of Service],{"ambu*","Lodg*","Meal*","special*","Mass*"}))

    What am I missing with the Wildcards and {}. It's a 145000 + row file

  285. JHatcher says:

    Thanks the countifs definition and example really helped me resolve a calculation issue in one of the formulas I was using.

  286. TEJASWINI says:

    dear mam,
    i have the following data in sheet
    A B C D E
    i want to count "SL" or any other string combine with other. which countif cirteria should i use?

  287. Anas says:

    Countifs with multiple criteria as is not showing correct input
    result showing as 0

  288. JOLLY says:

    m from a hospital background, i do need to use excel a lot to complete ma audits and various reports. m having a problem with my audit data. there are around 18 columns and about 500 rows, data validation has been applied, which gives me 3 options for each cell (yes no and NA). It is similar to your example of "COUNTIFS for text values:: counting who passed all the subjects. but in ma sheet there are three options, out of these i want to count yes and NA together and neglect no. i tried number of formulas but couldnt get the right one. atlast i counted number of "no" first in a row then subtracting it from total which gives me the value of number of "yes and NA" in same. But i was wondering if there is a possibility of counting two texts together in a single row.

  289. Kelly says:

    Good afternoon,

    If using =COUNTIF(C5:C21,"X")/ROWS(C5:C21) to get the percentage of what X equals.

    How do I edit this formula to know the percentage of what X equals if I need to add multiple columns and rows together?
    Columns C & D, Rows C5:C21 & D5:D21
    Columns C & E, Rows C5:C21 & E5:E21
    Columns D & E, Rows D5:D21 & E5:E21

    Thank you,


    • Randi says:

      Did you ever figure out this formula? I am trying to do the exact same thing and I cannot get it.

      If you did and could share it I would be much appreciative.


  290. Dean says:

    Hi Svetlana,

    I can't seem to get a simple formula to work:

    I have a grid spanning 31 columns wide and 5 rows deep. In any one of the cells I can have either, "M", "H", "S", "P" or nothing.

    The 5th row has to total up how many times in each of the 31 columns how many of each of the letters are shown and add them up. I'm currently trying to use this one in row 5 of column c for example:


    The box just returns 0 even when there are matching values.

    Can you help?!

    • Dean says:

      I literally solved it straight after posting this - typical! Took me two hours to admit defeat and post this question, then solved it almost immediately afterwards!

      I used this to fix it (I wasn't adding the values together!):

      =COUNTIFS(B5:B9,"P") + COUNTIFS(B5:B9,"h") + COUNTIFS(B5:B9,"s") + COUNTIFS(B5:B9,"m")

      Thanks for providing the inspiration to sort it!

  291. Ritche says:


    I hope to get help from you.

    I have in one sheet a serial number of products. In another sheet, it shows the dates to when customers called and complained about a product. What I would like to achieve is that I would like to know how many complaints a specific product received in a 7 days, 30 days and 90 days period using the product serial number as reference.

    Thank You for any response.

  292. Matt Keita says:


    I am working on a spreadsheet that has some strings in columns A2:K2.
    In a separate sheet I have in rows A2:A15 some strings (PP1, PP2, PP3...PP15)
    In cell L2 I want to be able to put a formula to count how many time any of the strings in A2:A15 appear in A2:K2.

    Thank you any help in greatly appreciated.


  293. Keith says:

    hello, I really need help with this.
    I have two sheets- the first one has an option to put in a date range as seen below:

    Start Date End Date
    14/07/2015 22/07/2015

    in the second sheet i have a yearly schedule for all my staff, but the way i have it laid out is the number of staff that will be in the office in the first column and the date will be in the second column, like below:

    staffed Date
    20 13/07/2015
    21 14/07/2015
    22 15/07/2015
    20 16/07/2015
    19 17/07/2015

    What i need the formula to do is check the date range in the first sheet against the numbers and dates in the second one and return a value.

    Example- i put a start and end date of my holidays into it, it checks the sheet and if i have less than 20 staffed on any of the dates specified it will say rejected. put if there is 20 or more staffed each day it will say approved.

  294. Shubham says:

    Dear Svetlana,

    I want to count the numbers when Cell value of Column A is less than Cell value of Column B of same row. Also want to apply the same logic for continuous 10-12 rows.

    Kindly suggest

    • Shubham,

      Because your task requires comparing 2 ranges, you need an array formula like this:
      =SUM((A1:A12<B1:B12) * 1)

      Due to it being an array formula, you must press Ctrl+Shift+Enter to enter the formula correctly.

  295. Rachel says:

    I have a countif function that is working:


    but I want to swap the date out for a cell reference e.g $AI$1 (will still contain the same date format). I cannot seem to get this to work.

    Please help!

  296. Bea says:

    I want to use the countifs formula when the value in cell E2 is present in the list PROSPECT AND when the value in cell L2 is in the list REGULAR.
    I use the formule :
    However it returns #VALUE!
    It works perfectly when I use a single countif formula : =COUNTIF(PROSPECT;E2)
    Can you help please?

  297. SR says:

    Hello Svetlana,

    I'm creating an wedding invite spreadsheet. I have four columns: NAME, GUESTS, RESPONSE, AMT ATTENDING. I would like to to create a conditional cell (AMT ATTENDING) on each row that will determine how many are attending. So, if the RESPONSE cell has 'N', the AMT ATTENDING cell on that should equal 0. If the RESPONSE cell equals 'Y', the ATTENDING cell should equal the GUESTS cell on the same row.

    Is this possible? Thank you so much!

  298. Jose says:

    Hello Svetlana,

    the following formula is giving me some sintax error in the last field. I am trying to create a criteria ">=" that refers to another spreadsheet.


    What would be the right expression? Thanks a lot,


  299. Rami says:


    I have a table to do with letting of properties, table has following info 'property' 'type of property''applicant names' 'type of applicant, UOCC, HGR, FTA, TRA, MTA' and also the date that a property has been let.

    I would need to count per calendar month, how many properties were let per applicant type?

    Your help is much appreciated.

    Thank you

  300. Chalo says:

    I need to use the COUNTIFS to count ID#’s that are between specific dates (for example: 6/22/2015 -6/28/2015; 6/29/2015-7/5/15; 7/6/15-7/12/15) but I have more than one entry for the same ID# and I don’t want to count duplicate ID#s.
    ID# Dates
    UTIC05019_000 7/14/2015
    UTIC05019_000 7/14/2015
    UTIC05019_000 7/14/2015
    UTIC05019_000 7/14/2015
    UTIC05021_000 6/24/2015
    UTIC05021_000 6/24/2015
    UTIC05021_000 6/24/2015
    UTIC05022_000 6/27/2015
    UTIC05022_000 6/27/2015
    UTIC05023_000 6/28/2015
    UTIC05023_000 6/28/2015
    UTIC05027_000 6/28/2015
    UTIC05027_000 6/28/2015
    UTIC05030_000 6/26/2015
    UTIC05030_000 6/29/2015
    UTIC05030_000 6/29/2015
    UTIC05030_000 6/29/2015
    UTIC05030_000 7/1/2015
    UTIC05032_000 7/8/2015
    UTIC05032_000 7/8/2015
    UTIC05032_000 7/8/2015

  301. Paul says:


    Do the different parts of the countif formula all have to be contained within the same worksheet?

    I have created a report which has data on one tab, and a query form on a second tab. The countif formula is looking at concatenated fields which I want it to return a value of the number of occurrences of a selected concatenated value (such as contract number and month). I am getting a zero when using the countif formula which uses the data as the first part of the countif, but the second part of the countif formula is from a cell in my query form.

    If I do a countif where both table and criteria are on the same tab, I get the value I'm expecting.


  302. kim says:

    Hi I am trying to use a countifs formula for the following situation:
    I have multiple staff and I want to get a tally of how many times each staff puts in overtime for each reason for overtime.
    I have been successful with this but I want to take it to the next level and find out the total amount of time corresponding to each reason for each staff member. So the formula is referring to a separate tab in excel and basically saying that if B4:B30 says "X" and c4:c30 says "Y" I want the value of D4:D30 that corresponds to this condition to show in the cell. Is this possible?

    • Hi Kim,

      Let me check if my understanding of the task is correct. so, if B4 says "x" and C4 says "y", you want to pull the value from D4. If so, you can enter the following formula in row 4 and then copy it down to other cells in the column:

      =IF(AND(B4="x", C4="y"), D4, "")

      If you want to sum the numbers in D4:D30 that correspond to those conditions, then you can use the following SUMIFS formula:

      =SUMIFS(D4:D30, B4:B30, "x", C4:C30, "y")

  303. Melissa says:


    I have two columns (all text). The first column is a list of names (could be repeated) and the second column is a list of factors (i.e. "ontime", "late", etc.). I want to know how many times a certain name (from the first column) hits each factor from the second column. Hopefully this is clear.

    Thank you!

  304. Rick says:

    Good Morning,

    I am so thankful I found this site.
    I am looking for a simple formula to find all 4s in column b, that has a code of X3,Z1, and Y9 in column E, PLUS all 5s in column B, that have blanks in column D, with codes of X3, Z1 and Y9 in column E.
    Can this be done? If not, thats fine, I will complete it the same as always.
    I appreciate all your help.


    A B C D E

    5659 4 91B 91B2 Z1
    2935 4 68C 68C2 Z1
    6572 4 92W 92W2 Z1
    8675 4 91D 91D2 Z1
    7627 5 12B 12B2 Z1
    7450 5 19D 19D2 X3
    9171 5 36B 36B2 Z1
    3503 5 11B 11B2 Z1
    5759 5 36B 36B2 X3
    5062 5 15D 15D2 Z1
    9088 4 91E Y9
    1665 4 68W 68W2 x1
    7212 4 68W 68W2 x1
    4539 4 15U 15U2 Z1
    2579 4 74D 74D2 Z1
    8052 5 14H X3
    2423 5 15U X3
    5228 5 12P Z1
    9080 5 19D Z1
    9076 5 11B x1
    691 5 74D x1
    3504 5 31D x1
    8594 5 14E x1
    919 5 11B x1
    4518 5 11B X3

  305. Craig says:

    I am trying to use a count function, but struggling to get the formula to work.

    I have an area that will be filled in with different dates. On a separate sheet I have a running date column. I want to count how many boxes have a certain date in it.


    This is the formula I'm currently using, which works fine, but I cant drag it down. I've tried doing the alternative:


    (B8 is where the 14th August is located). This does not work.

    Please help.


  306. Hassan says:

    How chose two fromm three number the best.
    A: 30
    B: 20
    C: 10
    Two the best 30+20=50

  307. Jonathan Barham says:

    I am trying to count the number of people who have done an intervention by the place from which they come from. I am taking this information from another sheet. The thing is they have done up to four interventions and the intervention columns are not in direct order of one another. So I am trying to count the number of people say playing football which could be in four columns by the place the live.

    I have tried =COUNTIFS('USER SHEET'!AC2:AC10000,Intervention!D3,'USER SHEET'!B2:B10000,Intervention!C4,('USER SHEET'!AF2:AF10000,Intervention!D3,'USER SHEET'!B2:B10000,Intervention!C4,('USER SHEET'!AI2:AI10000,Intervention!D3,'USER SHEET'!B2:B10000,Intervention!C4,('USER SHEET'!AL2:AL10000,Intervention!D3,'USER SHEET'!B2:B10000,Intervention!C4))))

  308. colleenwaters says:

    Hello. I’m trying to count unique text values in column A that meet date criteria in column G as determined by one cell (H158160) that contains a drop down list of dates selected by users. The intention is for them to be able to select the date from the list and see the count of account numbers, the sum of units sold, and the sum of revenue. I’ve got all of the formulas working except for the count of account numbers, because I’m having trouble with the COUNTIFS formula. I’ve been able to count unique values using the array formula {=SUM(1/COUNTIF(A2:A796,A2:A796)}, but I don’t know how to set these multiple criteria. Hopefully you can help. Thanks in advance!

  309. abraham says:

    i have got a big data of employees working for a mining comapany, i want to know the number of employees that come for breakfast, lunch and dinner differently. how can i do that?

  310. abraham says:

    i have got data of employees for a mining company. i want to count those that appear on the breakfast, lunch and dinner differently. how can i do that?

  311. Wale says:

    Outlet type: A, B, C, D

    Expected Product range by outlet type

    A - 20g,30g,170g,410g,400g tin,400g pouch,uht fc,uht lf
    B - 20g,30g,170g,410g,400g tin,400g pouch
    C - 20g,30g,170g,410g
    D - 20g,30g,170g

    Product presence report (Yes means available while No means not available)
    Peak 170g - No
    peak 410g - Yes
    peak 30g - Yes
    peak 20g - Yes
    peak 400g Tin - No
    Peak 400g P - No
    Peak 900g - No
    Peak uht fc - No
    Peak uht lc - No

    Hi Svetlana, i have above merchandizing report data. Based on the outlet type and the lists of products expected there and the product presence report, i want a formula that will count and return the number of products present in the outlet by considering the type of outlet (A,B,C,D). Thanks

  312. rukuruku says:

    YES NO

    hi,how can I automate, such that when is placed in a NO column then the percentage reduces eg

    YES NO


  313. rukuruku says:

    Svetlana Cheusheva,hope yo helping me,thanks

  314. andrea says:

    I am working with excel to draw up characters in columns consisting of random alphanumeric combinations. For ex: TRG, 0RG, 12TT, in column organization. I want to list the number of each individual character and how many times the character appears within each column. .. IE: 0=1, R=2, T=3. The countif function completely works for alphabet characters, but not for numeric when next to alpha.

    The 0 numeric when beside an alphabet character is simply not recognized. I've attempted some * with no luck..I'll keep reading could you please provide some insight?

  315. Melissa says:

    Hi Svetlana,

    I'm using excel mac and I'm trying to countif a date range and using a separate worksheet the name of the worksheet is KIDS and the column in that worksheet is R. Please where am I going wrong, below is an example;

    =COUNTIFS(KIDS!R:R, ">01/07/2015", KIDS!R:R, "<31/07/2015")

    Thanks heaps :)

  316. Cathal says:

    Hi there,

    I have to get 2 columns of data from a data sheet.

    Basicly Column X is a name and Column Y is an answer of one of six results.

    While Column X will remain the same I need to see how many of each of the results appear for Coloumn y of the 6 criteria.

    I used =COUNTIFS(A1:A941, "JOHN", C1:C941, "CLOSED")

    But it wont tell me how many of the columns under Johns name are closed

    Can you help?

  317. Tommy says:


    I want to count the number of cells from "today's date" to a specific charater, lets say S.

    For example: If todays date where 15:th of july and I have my sheet like this:

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
    S P T L L

    I want it to count the number of cells, regardless if the cells are blank or have a different character(P,T or L) from 15th of july until it finds the first S, thus 15-3=12.

    Hope you can help.


  318. MBIBSC85 says:

    Below is my formula, it is counting everything in the first () and not the second. Please help.

    =COUNTIFS('513TH BYNAME'!G2:G1500,"09L5",'513TH BYNAME'!H2:H1500,"OO")+ COUNTIFS('513TH BYNAME'!I2:I1501,"9J,9Y,9T,9V,9R,9Q",'513TH BYNAME'!K2:K1501,"*2015")

    Does not count the second COUNTIFS('513TH BYNAME'!I2:I1501,"9J,9Y,9T,9V,9R,9Q",'513TH BYNAME'!K2:K1501,"*2015")

  319. Javier says:

    Hi Svetlana -

    I was wondering if you could help me on how I can use Countif or Countifs to check a list/colmun of different words, tell me how many times it appears in a search of about 300 rows. I have about 70 different words to search in it would be time consuming if I just simply worked one at a time using =countif(A1: LP12,"Different Words to Look up").

    I believe there is a formula that I can just either drag and use to look all at onces.



    • Hi Javier,

      If you have a list of those 70 words in some column, you can reference the first cell in the formula, say $A13, and then copy the formula down to other cells:
      =COUNTIF($A$1:$LP$12, $A13)

  320. Bob says:

    I'm trying to create a countif formula to avoid duplicate records. Two of my headings are "Name" & "Date" my goal is to NOT enter a name using the same date more than once. Can you help?

  321. Mark says:

    Svetlana, I've got a workbook where I'm tracking attendance at various classes. Each class and attendance roster is a separate tab. I've got an employee summary tab where I've set it up to count the number of times each employee appears across the various tabs. I'm using this formula =SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$2:$A$15&"'!A1:M200")A29)) for that and it's very successful. A2:A15 are the tab names, A1:M200 the range on each tab to search and A29 is the employee name. So far so good.

    Now I need to do a similar count, but only when the class is required by the person's title. I had to set up the names of the various tabs (see above) in order to make the previous formula worked. So I just made it a grid by adding titles across the top and indicating which class was required for each title. I'm just lost now on how to make that work with a formula. Any help is appreciated.

  322. mbadawi says:

    how can i count 2 categories in same raw ex n , b
    n b n b n b b b b b n b n b n b n b n b

  323. Cindy says:

    I want to count the number of concentration values in a specific range (4,000) for a subject list. However,each subject (listed by patient ID) can have 2-5 concentrations (each listed in a separate row), and some are in more than 1 range (e.g. low and high). I would want to group by patient ID, keeping all their samples together, but sort the data into those patients with all samples being in the low range, all in the high range, all in the middle range, and then another group with those that are mixed ranges.

    Any help you could offer would be greatly appreciated.

  324. sidheswar says:

    2-2-2= 6
    2-2-2= 6
    6-4-6= 16
    4-4-8= 16
    2-4-2= 8
    4-4-4-2= 14
    how ca we add these items and get sum

  325. Partha Dutta says:

    Hi Svetlana,

    I am having a problem with countif function. I want to test a column data with dates and count them by the weekdays. Here is what I wanted to do, R18=COUNTIF(B3:B12,WEEKDAY(B3,1)=Q18). It is not working.
    How to count a column data by testing those datas with another function(Weekday)?

  326. Enrico MArgioni says:


    I am trying to use a countifs combined such as shown below. What I am am trying to do is with to check each of the cells referenced in the range to see if the text in the criteria is present. Overall intent is to determine a percentage of compliance. So in this case if each of the cells referenced are determined to be met in the criteria check then the cell where this formula would be 100%. If only part or none of the criteria are met then the cell would be the percentage based on the counts. I have tried adding an If before the count but it seems something is wrong.

    =(COUNTIFS(BA5,"2008 or Above")+COUNTIFS(BA5,"Not Applicable")+COUNTIFS(BC5,"Has Maintenance",BC5,"Not Applicable")+COUNTIFS(BR5,"All Physical Servers Have Maintenance",BR5,"Not Applicable"))

    thanks for your time. Enrico

  327. TONY says:

    Is there a way to calculate the amount of hours in the same cell if I inputted 8am-4pm? the goal is to use one row to input the time as shown above and a row to show the total amount of hours. in this case would be 8.

  328. patrick says:

    wow thanks, i know now how to use count-if

  329. Jerin says:

    Hi team,

    I had a query about countif and sum if and I have sent an email to the support email ID. I just want to know how long does it normally take to get a response.


  330. Jeff says:

    Hi Svetlana,

    I am attempting to count rows that contain a specific piece of text in the C column as well as a specific number in the I column. The formula I am attempting to use is:


    The formula returns a #VALUE! error but I am unable to locate it. Would you please point out what I am doing wrong?


  331. rahul says:

    I want to search an array where one value is an exact match, and the second value falls within a range (specified by two columns – an upper and a lower bound)

  332. Chuck says:

    Hi Svetlana
    I am attampting to count repairs done by tool type (column A), serial number (column B), and technician (column C). I've tried this and it doesn't seem to be working:

    AA17 is a specific tool type
    AA10 is a specific technician

  333. shane says:

    hi there. hope you can help. i have table of 2000 lines. i have created count formulas to go over the entire data...=COUNTIF(J5:J4000,"=2")
    issue i have is that i want to break it down per person (far left hand column) then still use the same formulas to get the same formula but only for the person as well as the whole 2000 lines data.

    in the 2000 line data, there will be approx 15 names that i need individual results from... hopes this makes sense and you can help

    BILL 432 1
    BILL 4 1
    BILL 34 1
    BILL 325342 2
    BILL 5 2
    PETER 342 2
    PETER 5432 2
    PETER 5 2
    PETER 52308 2
    PETER 432 2
    PETER 5 2
    PETER 432 2
    PETER 5 2
    STEVE 3425 2
    STEVE 3425 2
    STEVE 434 5

  334. Quin says:

    Hello Svetlana,

    I need help analyzing data in Excel. I am working on my thesis and the data was sent to me from a program via Excel.

    1. I saw the how to create a drop down in excel. How can I convert the numbers in the excel file to for example yes = 1 and no = 2?

    2. Once I am done with that portion, do you have a link where I can analyze the information?

    Thank you

  335. James Burney says:

    Hi Svetlana,

    Could you please help? I am attempting to count a different range of between numbers and assign them a score if they hit the specific criteria.

    Monthly Unit Sales 100 - 500 = 1 point 500 - 1000 = 5 points 1000+ = 10 points.

    Curious to what formula I can apply as there are at least 8 columns requiring the same formula & I then need a total score when all columns are combined.


  336. Murali says:

    Whilst adding attendance using countif function if P = 1 shift and the person continues for the second shift which is marked as 2P = 2 how can the two be added? ie if there are 30 days and the person has worked 1 shift extra it should count as 31 Thanks Murali

  337. Shashi Bhushan says:

    Hello Svetlana,

    Hope you are doing great.
    I would like to know how to know if there are multiple combinations of two rows.
    For Example:
    Column 1 | Column 2
    A | value1
    A | value1
    A | value2
    B | value3
    B | value4
    C | value5
    C | value5
    D | value6

    Here 'A' from Column 1 has multiple values in Column 2, namely 'value1 and value2'.
    Similarly 'B has 'value3 and value 4'.
    Suppose there are thousands of such records.
    And I would want to know which elements in Column1 has different matches in Column 2.

    Could you please help out on how to get this.
    Any help is much appreciated.

    Thank you,
    Shashi Bhushan

  338. PONRAJ says:

    how to enter only 2 item in a CELL in ms excel 7. EX - IN A CELL ONLY YES / NO --- CAR / BUS

  339. Kody says:

    Hi, so this is my data:

    and the question asks to use the IF or COUNTIF function to find the number of countries (which is 5.... but what is the formula?)

  340. Andreas Funk says:

    I am having an issue related to this article that I just can't seem to solve.
    I get an excel dump every week with all cases handled by a support organisation.
    My job is to summarize the number of cases created by a member of a specific group of people
    I am also supposed to summarize the ammount of cases that have been created by one specifik user and where the case is assigned to a specifik case group.
    The file is in 4 collumns. Collumna A is case ID which is not interesting to my assignment.
    Collumn B is the name of the case group that the case is listed under.
    Collumn C is the time the case was created
    Collumn D is the name of the person that created the case

    Collumn D can contain up to 50 different names but I need to count the ammount of cases related to a specifik list of 12 names.
    I need to count them per day so between midnight one date and the next.

    Can this be done using a set formula, so that I can just paste the new excel dump in to a set worksheet and get the informations directly?

    Would very much appreciate your input.
    Best regards

  341. Rishi Bansal says:


    I am facing a problem to plot the value against "Logical_Text", Wherein Same "Logical_Text" are in Col. "A" (in multiple rows) and A Value has been given against one of the "Logical_Text" in Col. "B". I need to plot the Value given (in Col. "B") against the "Logical_Text" in Col. "A" on all the rows against "Logical_Text" in Col. "A".

    Please advise the action and formula for doing so.

    Thanks & kind regards

    Rishi Bansal

  342. Srikanth says:

    Hi Svetlana,

    I have read your comments.

    I have a data which a row consists of both Text (Y & N) and Dates (29/09/2015).

    I want to count the number of cells that contain both Y and date but donot count N.

    Please suggest me a formula to count number of cells.

  343. Melony says:

    In column Q, I need a formula that will count all of the cells that have ROH,EXEC,and STAFF. I tried using the format below but it did not work.

    =COUNTIF(Q11:Q275,"ROH") + COUNTIF(Q2:S11,"EXEC") + COUNTIF(Q11:Q275,"STAFF")

    • Alexander says:

      Hello Melony,

      In your formula, the second COUNTIF contains a different range, and this may be the cause of the problem.
      Try the following formula:
      =COUNTIF(Q11:Q275,"ROH") + COUNTIF(Q11:Q275,"EXEC") + COUNTIF(Q11:Q275,"STAFF")

  344. Liam R says:


    I would like to ask for some assistance in the COUNTIF formulas. I have a comprehensive list which gets fed information from another spreadsheet. It looks in these cells for the word(s) im looking for.Now in the same cells for each one, i'm looking for about 100 different words which means i need to write the "word" in every formula.The cells are separated and cant be group-selected Overall due to my spreadsheet and how broad it is for what I want and need, ill need to change the word in the equation approximately 1600 times. Not really in the mood to do that if possible.

    Heres the formula example Im using right now. Can I reduce the size of this formula and make it more concise. If you want me to send an example spreadsheet, im willing to do so.

    =COUNTIF('Group A'!E6:I6,"")+COUNTIF('Group A'!P5:T5,"")+COUNTIF('Group A'!E13:I13,"")+COUNTIF('Group A'!P14:T14,"")+COUNTIF('Group A'!E21:I21,"")+COUNTIF('Group A'!P22:T22,"")

    Thanks :)

  345. Fiston says:

    I am struggling with my data in which I have a list of people with their ratings (ranging from 0 to 5) the past 5 years and I need to separate the people who had at least a rating of 4 twice during the past 5 years.
    I tried a combination of IF and Countif but can't seem to get it right. Your help would be highly appreciated.

  346. Rishi Bansal says:

    Hi Alexander

    Have send the Sample Workbook for your ready ref.

    Kind regards

    Rishi Bansal

  347. K.Nagasundar says:

    which formula used for the below table

    Book stock Physical stock Difference stock (+)stock (-)Stock

    Physical stock (-) Book Stock is difference stock, if difference stock comes (+) go to (+)Stock otherwise comes (-) go to (-) Stock

    how to use the If formula, explain clearly

  348. ejoy says:


    Good day!

    I was hoping you could help me on this, I need to create a statement comparing values only in one column. If there are duplicate values, the first value will be tagged as "primary" and the succeeding "secondary"
    Is this possible?

    Thank you in advance!

    Kind regards,

  349. Rajkumar says:

    True, Microsoft Excel 10 is the value of the F column. 6 When the value is put in the column H 4 value in column G to be put. Examples of this type of formula.

  350. Rajkumar says:

    True, Microsoft Excel 10 is the value of the F column. 6 When the value is put in the column H 4 value in column G to be put. I have required this type formula.

  351. Jo says:


    I need to count the number of postal codes within a certain range, e.g. between 2420 and 2490. I have been using the countifs function, however it does not seem to be counting all the postcodes that exist within the range specified (=COUNTIFS($A$2:$A$10000,">=2420", $A$2:$A$10000,"="&$H2,$B$2:$B$10000,"<="&$I2) - where h2 is a date and so is I2). I have also found that this formula is not counting all the dates that fall into this range in column b. Is there something also wrong with this formula?

    I would appreciate any help I can get!

    Thanks in advance,

  352. Jo says:

    Oh dear - that did not post correctly!

    The first formula is =COUNTIFS($A$2:$A$10000,">=2420", $A$2:$A$10000,"="&$H2,$B$2:$B$10000,"<="&$I2) (where h2 and I2 are dates) and b is a column of dates

    Hopefully that helps clarify.


  353. Rhett says:

    I'm trying to calculate how many times a state appears in a range. =COUNTIF('[2State Calendar.xlsx]Calendar'!$J$6:$K$11,"*NY*")

    I want to calculate multiple states appearing in the same range. Is this possible?

  354. Dhavl Oza says:

    I want to compare two rowed cell values and find minimum between both,Minimum result number will be multiply by 3.5 and stored to third cell.


  355. Syida says:

    I need to simplify no of item for selected branch in a month? or to combine below countif formula.



  356. John Howell says:

    I've hit a brick wall with sheet that I am trying to Create to create a rota from a list of tasks that need to be performed at different times of the day. I have a list of tasks that must be performed and a person's name is input against it and the name is the automatically transferred to the rota by the formula.

    I have set up the rota so that the cell under a specific time and persons name tests if the person has been allocated the task with the formula =IF(F8="Jane","task1","")
    so that when Jane looks across the rota against her name she can see the tasks that have been allocated to her. This is working perfectly.

    The problem is that at 12:00 Jane might be allocated any one of two tasks that need to be completed at the same time or no task at all. I have been trying to use the above formula and adding a second If statement onto it to test another cell if the first statement results in FALSE.
    and this is not working.

    Can you help?

  357. Chris says:

    In Example 4. COUNTIF formulas with OR logic is there a reason you did not use the sum of the array?
    =SUM(COUNTIFS(A2:A11,{"Product1","Product2}, B2:B11, 0))

    I am also wondering the best way to count rows with multiple OR logic? I know the following doesn't work with 2 columns of OR logic, but what would be the best way to accomplish this?
    =SUM(COUNTIFS(A2:A11,{"Product1","Product2}, B2:B11,{"0","14"}))

  358. Eric says:

    This is my current formula for two different columns: =COUNTIF(B12:AF12,"lt") and =COUNTIF(B12:AF12,"d").
    I am tracking Time, Attendance and other things for employees. If they have a late on one day (cell) I put LT and it counts it in the LT Column. If I put D for Disciplinary action it will count it on the D column but what if I have a late and Disciplinary action on the same day? I want it to count it on each different column and cell needed.
    Thank you,


  359. C Bullock says:

    I would like to use a countif function in a pivot table. I have four columns of data with different suspension codes in each column. I might have violation "I01" appear in any of the four columns. I want to count how many times "I01" is in any of the four columns. I love the pivot table because then I can use them to count how many times this infraction is reported at each of the schools I work for. Any suggestions would be appreciated.

  360. apsar says:

    hi dear i dont have knowledg of excel formulas so plz guide me how learn a advance excel formula.
    and i need your advice.

  361. MOhamed Ibrahim says:



  362. Mike says:

    I have 5 columns,
    1. Date In
    2. Time In
    3. Date Out
    4. Time Out
    5. Date to count
    I have a long list of these dates and times and want to find out by date and hour how many occurrences.

    9-10a etc.

    How do I assemble countifs to produce the hourly data?

    Countifs(A1:A31,E1,B1:B31,">="&TIMEVALUE("7:00 AM"),B1:B31,="&TIMEVALUE("7:00 AM"),D1:D31,<="&TIMEVALUE"("8:00 AM")

    I can't get to this to work so it shows between the in and out times and date.

    Thank you,

  363. Lyle says:

    =COUNTIFS(C12>F12,">=1", C21>F21,">=1") ???

    Basically looking to have a total for wins and losses in a sports results column.

    So if 71 (C12) is greater than 70 (F12) give it a value of 1 (a win), then add that to the next line (C21) if that is a value of 1 as well.

  364. Humberto says:

    Can you please help?
    - M M N N
    M V V D D
    M V V V
    N D V D E
    N V E E
    WE need do know how many "V" we have in the table with the conditions (Horizontal/Vertical) M/N ; M/N ; N/M ; N/M
    The result is:
    M/N = 1
    M/M = 4
    N/M = 2
    N/M = 0

    Many thanks in advance.

  365. Taylor says:

    I am working with something like this:

    load# trailer# scac loc
    uc856489 85694 SXTI y182
    54852 SXTI y345
    uc854585 95484 TAMI y652
    45584524 6382 TAMI Y212
    54585 SXTI Y121
    UC845845 12548 JRWS Y222

    I'm trying to find a rule that will let me see if there is get a count of empty trailers on the yard by scac code "SXTI" (COL C) BUT I don't want to calculate the loaded SXTI trailers (WHEN COLUMN A SHOWS A 3 STARTING WITH UC* OR 455) I was going to make a line for each scac code on a different sheet with the rule cell next to it just showing the number. Is there a way that will work for this? I have tried many combinations but can't seem to get it to work.

  366. shilna says:

    I want to count how many nonzero cells exists. All cells are time formatted. I am populating with elapsed time.
    Column A - 0:00
    Column B - 8:05
    Column C - 9:22
    I should get result as 2.

  367. Kim says:

    I am trying to figure out a formal that would highlight a certain cell if a X appears a certain number of times within a certain data range. I am trying to work on an attendance sheet where i need the persons name to highlight if the attended 3 items in one data range and 2 in another data range and have data in another field that contains a drop drown list. I think i need to do a sum countif but I can't figure out how to set to highlight if a certain number within the data field is meet.

  368. DAVZ says:

    help on how to set COUNTIF to count only cell with >=25% but <=29% only,, meaning count only cells when it contains 25% and below 30%

  369. beesee says:

    From the example above, if you use the following:

    =COUNTIFS(B2:B11,"=0", C2:C11,"=0", D2:D11,"=0")

    it will count from the row only if all three meet the criteria

    however if you do:

    =COUNTIFS(B2:B11,">0", C2:C11,">0", D2:D11,">0")

    It will count from the row if any one, two, or all of the values is greater than 0

    q1: how is that logical?
    q2: How do I do the operation so that it counts only if all three satisfy the criteria

  370. Corey Worster says:

    I'm trying to get a total count of the values in one column, i.e. "Y100" and with all rows that contain that y100 count the number of cells in another column. i.e. "A". I've tried =COUNTIFS('Mods (Entire)'!B:B,"Y100",'Mods (Entire)'!C:C,"A") and =COUNTIFS('Mods (Entire)'!B:B,"=Y100",'Mods (Entire)'!C:C,"=A") both come back with a count of zero. Any and all help is greatly appreciated, will send sample if needed.

  371. Sanjeev Kathuria says:

    I have data in Column F. The data is like either "M" or "MH", either "E" or "EH", either "N" or "NH". This is for counting Shifts.

    I am using formula to count : =COUNTIFS(F4:F30,"=E",F4:F30,"=EH") for counting Evening Shifts. But it is givining me value as 0 (Zero). Please let me know where am I wrong.

    Thanks for your help in advance.

  372. Sharat says:

    Hi Svetlana,

    If there are two columns A and B with following data:
    A B
    Apple 1
    Banana 1
    Orange 3
    Apple 1
    Apple 1
    Banana 1
    Banana 1
    Banana 1
    Banana 1

    In the above data I would first like to search in column B for value 1 and then count only unique values in column A in such a manner that the function returns 2 (i.e., Apple and Banana). Is this possible and if so how can it be done?

  373. chuman says:

    when i scroll from one column to another column it have changes of his values



    another one =+COUNTIFS($G$5:$G$38,"ACTUAL",$H$5:$H$38,"D") only copied where i want =+COUNTIFS($G$5:$G$38,"ACTUAL",$I$5:$I$38,"D")

  374. ravi says:

    I have two columns, column 1- Dates, column 2- Names.
    01/02/2015 ARYA
    01/02/2015 ARYA
    01/02/2015 ARYA
    02/02/2015 ARYA
    From the above example, I want the count to reflect 2, in other words I would want it to count as a single occurrence per day i.e., 01/02 - one occurrence & 02/02 one occurrence. Please help me with this. Please help me without date ranges as i need to use it for the entire year for different names.

  375. Yuvaraj says:

    Dear All,

    I need your help to show the total time spent between multiple two cells repeatedly.

    For example, I'm starting my work by capturing the start time in A1 & ending at A2 and I'm getting the difference in A3 using the formula =A2-A1. After a short break, I'm again starting the time in A1 & ending in A2. Then what is the formula to be used to calculate the total timing spent in A4.

  376. chuman says:

    my 1st column data is- =+COUNTIFS($G$5:$G$38,"ACTUAL",$H$5:$H$38,"D") this one

    when i scroll it 2nd column the data is- =+COUNTIFS($G$5:$G$38,"ACTUAL",$H$5:$H$38,"D") this one

    my question is why not data scrolled by column reference,where ever the data wants =+COUNTIFS($G$5:$G$38,"ACTUAL",$I$5:$I$38,"D")

    plz chk and tell me the soluation



  377. Brent says:

    Trying to create a count for check (Tick) marks (where "P" is a tick). I can have multiple ticks per box in Excel. Currently it wants to add each tick. If I add 1 more tick to a box (2 ticks in a box) it will subtract it instead of add on to my current total. How do I fix this?

    My current syntax:

    =COUNTIFS(C7:C25, "P", C7:C25, "P")

    Please help. I need to be able to track my work.


  378. swati says:

    There's a File A and a File B, both in xls
    Both have the two columns each of the same names, say, "1" and "2".
    File A has 100 records. File B has 150 records.
    We need to add a column "3" to File B, and put a value 'NotFound' in it if the value in "1" is not found anywhere in A's column "1".
    After that is done, we need to repeat, in reverse. That is, add a column "3" to File A, and put a value "NotFound" in it if the value in A's "1" is not found anywhere in B's column "1".

  379. Faruq says:

    Trying to simplyfy the below functions:
    C D
    0 On Board
    0A On Board
    3 On Board

    =COUNTIFS(HK!C4:C8,"=0",HK!D4:D8,"=On Board")+COUNTIFS(HK!C4:C8,"=0A",HK!D4:D8,"=On Board")+COUNTIFS(HK!C4:C8,"=1",HK!D4:D8,"=On Board")+COUNTIFS(HK!C4:C8,"=1A",HK!D4:D8,"=On Board")

    Tried this ... but no luck :(
    =COUNTIFS(HK!C4:C8,{"=0","=0A","=1","=1A"},HK!D4:D8,"=On Board")

    Need urgent help please..

  380. trent says:

    Ok here is my question I have a multiple part number in one row and a serial number for each part number entered in a second row. I would like to count only the highest serial number for each of the different part numbers is this possible?

  381. David says:

    Hi Svetlana. Is it possible to use Countifs to pick up one of the criteria from a specific cell? I have tried, but it only ever returns a 0 value.

    For example, I have a list of companies in column A, the month in which an order was delivered in column B, and a note of whether the goods were OK or faulty in column C. I want to find out how many orders were delivered faulty in a given month.

    The formula
    =COUNTIFS(B3:B100,"=03 (14/15)",C3:C100,"=Faulty")
    works fine, but would it be possible to use another cell (let's say B102) to enter the month I am looking for and get Countifs to pick up and use the criteria from that cell?

    Many thanks.

    • Hi David,

      If my understanding it correct, you want a formula to "extract" a month number (03) from values in column B. If all the values in B have the same pattern like 03 (14/15), including a space before the opening parenthesis, you can use the following wildcard in criteria1:

      =COUNTIFS(B3:B100, "03 (*", C3:C100,"=Faulty")

      As for entering the month is a separate cell, probably it's also possible, but I cannot think of such a formula at the moment, sorry.

      • David,

        Here's how you can count cells based on the month number in cell B102:
        =COUNTIFS(B3:B100, B102&" (*",C3:C100,"Faulty")

        Important! You should enter the month number in cell B102 exactly as it appears in column B (03 in your example). And since Excel cuts off leading zeros in numbers, 03 shall be entered in cell B102 as text (Home tab > Number group > Text).

  382. Sasha says:

    How do I make a formula that states that column D is 30% of Column D?

  383. dhaval kartikeya says:

    i have a data in excel sheet with cell content like this
    the a 1,2,3
    the p 1,2,3
    how do i count the number of commas in the range with cell content.
    i want the outcome like this
    cell_contain count
    "the a" 3
    "the p" 3
    i'll be very thankful if you provide answer for my query

  384. Greg says:

    Hi Svetlana,

    I'm trying to count the instances a value is less than zero given two dynamic column ranges match. They are named ranges 'universe' and 'markets'. I have the following formula to give me the total number of instances that I have a match but I'm not sure where to add the criteria to return how many of these matches are less than 0.

    Thanks for having a look!

  385. Mayur says:

    Hi Svetlana,

    What formula can be used to find the count of cells that contain the text that is present in some other cell?

    For eg. i can hardcord the formula like this: COUNTIF($E$3:$W$17,"Svetlana")

    But instead of "Svetlana", i need to give the cell address.

  386. Sonia says:

    Happy New Year Svetlana!

    I would like to know how to find text in a range on a sheet that will display in a cell based on the criteria inputted in adjacent column:

    So, I have an excel sheet "other sheet" showing a database of reg plates and vehicle type for a fleet of vehicles. On another sheet "current sheet", I need the vehicle type to display automatically in one column (see below where it says formula) when I type the reg plate number in the adjacent column.

    THANKS :)

    A B 123 NISSAN
    1 123 formula 321 LANDROVER
    2 321 456 BUS
    3 456

    • Sonia says:

      REG 123, 456, 345

      REG 123, 456, 345

  387. yash pandey says:

    Hi Svetlana Cheusheva Mam, Please solve my problem. There are subject codes and grades scattered in various columns & rows in excel sheet. How can i extract counted grades & codes in one cell of excel sheet jointly. What formula use in it. for example excel sheet given below yellow for your knowledge subject code 41 and next row grade A1 A2 B1 B2 C1 C2 D1 D2 .HOW TO COUNT TOTAL 41 A1 IS ............?
    RAM 30 99 A1 41 95 A1 30 99 A1 41 95 A1
    SHYAM 41 99 A1 48 95 A1 41 99 A1 48 95 A1
    RAJ 30 94 B1 41 88 A1 30 94 B1 41 88 A1
    PHILP 37 92 A2 42 80 B1 37 92 A2 42 80 B1
    PIKASO 41 80 A2 42 91 A1 41 80 A2 42 91 A1
    PHAS 37 93 A1 42 81 B1 37 93 A1 42 81 B1
    PARI 37 90 A2 42 70 B2 37 90 A2 42 70 B2
    RAJ 30 88 A2 41 68 B2 30 88 A2 41 68 B2
    RAMCHARAN 41 93 A1 42 82 A2 41 93 A1 42 82 A2
    ANUPAM 41 77 B1 42 82 A2 41 77 B1 42 82 A2
    VIJAY 41 80 A2 42 78 B1 41 80 A2 42 78 B1
    VISHAL 41 82 A2 42 73 B2 41 82 A2 42 73 B2
    VINAY 37 82 B1 42 65 C1 37 82 B1 42 65 C1
    VIMAL 41 72 B1 42 67 C1 41 72 B1 42 67 C1

    • yash pandey says:

      please give reply me as soon as possible.

      • yash pandey says:

        RAM 30 A1 41 A1 30 A1 41 A1
        SHYAM 41 A1 48 A1 41 A1 48 A1
        RAJ 30 B1 41 A1 30 B1 41 A1
        PHILP 37 A2 42 B1 37 A2 42 B1
        PIKASO 41 A2 42 A1 41 A2 42 A1
        PHAS 37 A1 42 B1 37 A1 42 B1
        PARI 37 A2 42 B2 37 A2 42 B2
        RAJ 30 A2 41 B2 30 A2 41 E
        RAMCHARAN 41 A1 42 A2 41 A1 42 A2
        ANUPAM 41 B1 42 A2 41 B1 42 A2
        VIJAY 41 A2 42 B1 41 A2 42 B1
        VISHAL 41 A2 42 B2 41 A2 42 B2
        VINAY 37 B1 42 C1 37 B1 42 C1
        VIMAL 41 B1 42 C1 41 B1 42 C1

        Sub. Code Total code A1 A2 B1 B2 C1 C2 D1 D2 E TOTAL
        30 6 result here
        41 20 8 6 4 1 0 0 0 0 1 20 IT IS MANUALLY COUNT AND WRITE
        37 8
        48 2
        42 20


        There are subject codes and grades scattered in various columns & rows in excel sheet.
        How can I extract counted grades & codes in one cell of excel sheet jointly. for example code 41 get A1, A2 B1 B2 C1 C2 D1 D2 & E. for example given below.

  388. yash pandey says:

    Please reply soon in my mail id.

  389. Joey says:


    I have 2 column in I need to count the date on column "B" with the corresponding text on column "A"
    I want to count this : Z13-BW20D IF I HAVE CORRESPONDING DATE ON COLUMN "B"

    Can some giving me a little help for this one?

    ___________( column A)_____ (Colum B)

    KPC164-FAB-Z13-BW20D-01#0001 19-Dec-15
    KPC164-FAB-Z13-BW20D-01#0002 19-Dec-15
    KPC164-FAB-Z14-BW20D-01#0002 15-Dec-15
    KPC164-FAB-Z14-BW20D-01#0003 15-Dec-15
    KPC164-FAB-Z15-BW20D-01#0023 05-Dec-15
    KPC164-FAB-Z15-BW20D-01#0024 05-Dec-15

  390. jOEY says:

    A B
    KPC164-FAB-Z13-BW20D-01#0001 19-Dec-15
    KPC164-FAB-Z13-BW20D-01#0002 19-Dec-15
    KPC164-FAB-Z14-BW20D-01#0002 15-Dec-15
    KPC164-FAB-Z14-BW20D-01#0003 15-Dec-15
    KPC164-FAB-Z15-BW20D-01#0023 05-Dec-15
    KPC164-FAB-Z15-BW20D-01#0024 05-Dec-15

  391. Mayra says:

    Hello, I have been trying to figure out how to come up with a formula that counts cells from two different columns but that both represent one person. For example, I have an admitted column and a discharge column for clients. I'm looking at the first quarter of the fiscal year which counts October, November and December. However, when looking at each month individually, for example, October has clients that were admitted in August so I am trying to come up with something that counts before October but not after October as well as before November but not after November, etc. Does that make any sense? I apologize, I have been working on this for a while.

  392. natasha says:

    i have a table of employees by department and then a breakdown per day of holiday days and i need to count the amount of holiday days by department by month. is this possible?

  393. ASIF says:

    my problem that conditional formatting in sheet formula that if date enter than red color how cell end count automatically red color


    how to count two cell red any formula

  394. jules says:

    I'm trying to use countif or countifs to count how many times a name shows up, the catch is there are many names, and I don't want to find all of them to do =countif(a5:a25,"JANE DOE"). Is there a way for excel to count and prompt the name & qty? I'm trying to calculate how many ECNs are assigned to individuals within a large group. Thank you for your help!

    • Hello, Jules,

      Please try the following:
      1. Select an empty cell in your table
      2. Go to the Data tab -> Consolidate
      3. Select Count from the Function drop-down list
      4. Highlight the column with names and the one next to it
      5. Tick the left column check-box and click OK.

      Hope this helps.

  395. rohit says:

    Count the number in Unic criteria. To say

  396. Zahir says:

    Hello, I have founded a very important function from this site.
    thank you very much.

  397. Raj K says:

    How can I calculate the number of leads received during specific time ranges. E.g. how many leads did I received during 0:01:00 to 0:30:00 and so on.

    I've 10,000s of leads received during 0:00:01 to 24:00:00. I am taking a time range of 30 minutes, 0:01 - 0:30, 0:31 - 1:00 an so on..

  398. Charu says:

    Hi I have a Excel Sheet, Where i have Numbers achieved by sales team like (In Column A)
    1005,25900,5000,2005,1598,5368 etc..now i want to use a formula where i need to get the answer like..
    if in Column a amount is upto 1000 then in column be it should count 2 points and if amount is in between 1001-2000 then 3, if amount is 2001-4000 then it should show as 5..

    can u help on this

  399. Mark says:

    Hi, I need a total count how often a student has achieved a grade of C or above (A*, A, B or C). The cells containing the grades are not next to each other so I need to have a range of data counted from separate cells and presented as one number.

  400. Daniel says:

    Hi Svetlana,

    I am attempting to create a formula which sums one cell based on two other cells meeting a certain criteria (month and a specific word).

    Cells A3:A2000 are where my dates reside.
    Cells B3:B2000 are where my cost resides.
    Cells E3:E2000 are where the word resides.

    If cells A3:A2000 has a date within January and cells E3:E2000 has the word Groceries, I would like to Sum cells B3:B2000.

    Thanks for any time you have in dedicating to this question.

  401. MAHESH says:


    I need help to complete my first excel document. details below.

    I need to add criteria in Validation like this (MV1601, MV1602, its needs to continue up to MV1654 ). Can anyone please help me to finish my document soon.

    Many Thanks

  402. Susanne says:

    I am trying to construct a very complicated count if formula that will track if my Cubscouts have completed certain NOVA achievements. http://www.scouting.org/stem/Awards/CubScout.aspx

    For Example To earn the CubScout NOVA Award: Science Everywhere

    Condition #1 Ai and Aii>1
    Condition #1 Bi and Bii>1
    Condition #1 Ci and Cii>1
    AND Condition#2 A:E>0 (pick one A thru E)
    AND Condition #3 A:C=3 (do all A, B and C)
    AND Condition #4 A:B=2 (do A and B)
    AND Condition #5 A=1 (do A)

    Each requirement will be listed in a row and then the individual boys will be tracked in the columns. I can send a spreadsheet with these entered if it is easier to visulaize it that way

  403. Shakthi says:


    I am very much happy to see your blog for all clarifying excel formulas..
    there are two sheets around 20,000 thousand lines the projects are repeated each lines had X value in one sheet and another sheet had Y value...

    My questions are...
    How to accumulate value project wise between X and Y and comparing the difference...without using pivot and v lookup formula...


  404. Stacy says:

    how would I countif the criteria is a date and I want to count if the year matches?

  405. D.A says:

    How to make start time and finish time between many times in excel?
    For example, we want to know start time, finish time, and idle time for each hub.
    The raw data :
    Order Number Time Hub
    A-1 1/1/16 6:49 PM BSI
    A-2 1/1/16 6:49 PM BSI
    A-3 1/1/16 6:49 PM BSI

    Expected result :
    Hub Total Packages Start Time Finish Time Idle Time
    BSI 81 1/1/16 6:49 PM 1/1/16 6:52 PM
    BNO 50 1/1/16 3:32 PM 1/1/16 6:25 PM
    CKU 49 1/1/16 3:35 PM 1/1/16 6:27 PM

  406. Angelozzz says:

    Hi how to make formula for this long spreedsheets

    Building Name Received
    building 1 Chain
    building 1 Chain how many chain received by building 1 ???
    building 1 cups how many cups received by building 1 ???
    building 2 Chain
    building 2 Chain
    building 3 Chain
    building 3 cups
    building 3 glass
    building 4 pencil
    building 4 pencil
    building 100 bag

  407. will says:


    I am doing a rota/roster based on our initials at work we rotate who works mondays. column a is date and column b is initials - how do i work out how many mondays people do by a formula?

    • Hello Will,

      Please try to use the following array formula:

      A1:A43 is the range with the dates here;
      $B$1:$B$43 is the range with initials.

      Once you enter the formula, you need to press Ctrl+Shift+Enter.

      If you don't get the expected results, please send a sample worksheet with your data to support@ablebits.com. Please include a link to this blog post and your comment number.

  408. Ed says:

    I need to count many numbers within the same cell.
    For example, (10,12,13,15,16,17,18,20) is located at J16.
    The numbers represent calander week numbers for an event.
    At the moment I am physically counting many hundreds of these cells, high risk of human error.
    Can excel count the above example would = 8
    I can't use different cells, as software importing this data cannot deal with many cell references, only one cell.
    Thanks, Ed.

  409. Abhijeet says:

    I use this formula to Overlap dates with more criteria =COUNTIFS($A$3:$A$16,H3,$C$3:$C$16,">="&I3,$B$3:$B$16,"<="&J3)

    But i want to return value how to use index match formula in this formula

  410. thawfeeque says:

    hi Svetlana, i have created a any year calander, now i wanted to mention the holidays. here in Sri Lanka the holiday not constant for every year it is differ the other years, i think if we created a holiday chart and can match to the calender? pls help me

  411. Mariah says:


    I have a spreadsheet that I am working on and cannot figure out if I can do this or if this is even possible.
    So what I have is 3 columns. I have dates in 2 of them. I need the 3rd column to perform an "If" or "COUNTIF" statement that is.. If the date in column 2 is greater or after the date in column 1, calculate the days late or the difference in column 3. Is that possible? Would love to know. Thank you!!

  412. Seth says:

    Hello, i am trying to count the number of customers in a list when each customer has a code of 13 or 18 and the same open dates. The last 2 in the list would "count"

    Code Date Opened Name id
    13 12/11/2015 Customer 4 63679
    18 12/11/2015 Customer 3 63693
    18 12/11/2015 Customer 2 63670
    13 12/11/2015 Customer 1 63715
    18 12/11/2015 Customer 1 63688
    13 12/12/2015 Customer 63770
    4 12/12/2015 Customer 63770

    I can't for the life of me figure out how!
    Any help would be greatly appreciated :)

  413. Ramki says:

    Hi Seth,

    The simple solution to your query is this:

    Step 1: Convert the data range to a Table [use Ctrl T] function.
    Step 2: When you have done this place your cursor on any part of the table and right mouse click and select Table and Total rows.
    Step 3: In the Code column select 13 and 18 and only those customers with the specified code will be listed.
    Step 4: In the date column select the date you want, for example 12/11/2015. The only the specified date will be displayed.

    The table can be expanded and more conditions via filter can be selected.
    Further, if you have Excel 2013 you can have Slicers as filters and visually you can select the criteria you want.



    • Seth says:

      Hi Ramki, Thanks for the quick reply!
      Sorry, but I mistyped. The customer can has to have code(s) of 13 or 18 and an additional code(s) between 1 and 27. In the example the customer has a code 4. The dates must also match.

      Basically i am trying to count the number of customers who opened multiple accounts on the same day, but one or more of the accounts has to be a 13 or 18.

      Hopefully this helps clarify.

      Thank you!

  414. Wendy says:

    My question is I have to work out the number of instances where the figures over a number of cells and rows fall outside of a specified range.
    ie: column D to I from rows 3 to 233 have numerous figures, I have to work out the number of instances these figures fall outside of 4 and 7.

  415. Vinay Shivhare says:

    Thanks Svetlana... This post has been of great help to me... :)

  416. Andrew says:


    I am trying to create an excel formula based on a tree risk assessment model with three variable factors:

    1) The size of part likely to fail
    2) The frequency of use of the area, and
    3) The probability of failure (PoF).

    Each variable has a range between 1 and 6. For example if the size of part equals 3, the frequency equals 2 and the PoF equals 2 then the overall risk is given as 1/4000.

    I have the table with all of the possible combinations of the 3 variables listed, each correlating to an overall risk ranking. My question is: Is it possible to create a formula based on the table to retrieve the specific risk ranking based on a random combination of the three variables?

    Any light shed on the topic would be greatly appreciated.


  417. Erik says:

    I'm trying to find the formula for; if a given number out if entered number in a designated cell is one of the designated numbers, a give text populates in assigned cell.
    * assigned roster number I.E. 300,400,500,600.. = Team 1
    * assigned roster number I.E. 325,445,515,618.. = Team 2
    * assigned roster number I.E. 312,422,5671,643.. = team 3

    With that: The formula will see the entered number from tab #2 cell A3 roster number.

    Text populates of assigned team in tab #1 cell B2 from that data.

  418. Ramki says:

    Hi Seth,

    I am sorry that I could not come back to you earlier as I was out of town for a meeting.

    RE your query - you can select any number of codes you want from the "Codes" column drop down filter. Once you have done that select the date from the Dates column drop down filter and your result will be got.
    Remember the sequence of using the dropdown filters - First the Codes and then the Dates.
    Use of Slicers would be more easier for simple conditions.

  419. Mary says:

    I need a range of cells to tell me a number when criteria from another cell range is met.
    EX: C3:C95 is where I need to know how many if range D3:D95 is "A"

    I have tried countifs in a variety of ways and can only get it to recognize 1 for any number placed in C3:C95

  420. Satya says:

    Please help me i have TAT data of the employee were i required Acquired tat for individual employee who is falling in which range like -
    1) 0 to 5
    2) 6 to 10
    3) 11 to 15...
    required employee wise split

    kinldy help

  421. Taimoor says:


    How can i count no.of employees working in different departments year wise.
    For example total no. of employees is 100, how can i know that how many employees were working in different departments in different years.
    In year 2015, 10 were working in accounting, 20 were working in admn. etc etc
    In year 2016, 35 were working in accounting, 10 were in R&D, 20 were working in admn. etc etc


  422. Gab says:

    Hi, Can you help me in generating a countif formula in my table? For example Cell-A states the Company Names, ex. A,B,C,D, then in Cell-B states the specific date they submit their reports, ex. 01/31/2015,02/12/206. The problem is I want to count the numbers of the report they submitted in a month. Many companies, Many reports submitted in 1 month, How can I use countif in this problem? please help me. I dont know how to use date in Countif.

    • Hi Gab,

      You can use the following formula to count reports, say, submitted in January 2016:

      =SUMPRODUCT(--(MONTH(B2:B100)=1), --(YEAR(B2:B100)=2016))

      Where B1:B100 are cells containing submission dates.

      To count reports for any other month / year, simply replace =1 and =2016 in the formula with the required numbers.

  423. Abhijeet says:


    i use this formula =COUNTIFS('Process Data'!$A:$A,Tally!$A3,'Process Data'!$C:$C,Tally!$B3,'Process Data'!$H:$H,Tally!C$2)

    I need to use in VBA how to use Countifs Function so please tell me code foe this

  424. Gurpreet Singh says:

    Hi ,
    I have one situation where I am stuck. I have two columns, one with car name and 2nd with number of sales in a year.
    Can i use count if to get what car and how many units are sold.
    I tried giving criteria as Camary and range as the no of units sold, but that did not work. I tried selecting both columns as range, but that did not work either.
    Please help