Jul
10

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. 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 the COUNTIF function is intended for counting cells based on a single condition in one range, while COUNTIFS allows using several criteria and ranges. In practice, however, you can use Excel COUNTIF with multiple criteria as well. So, the aim of this tutorial is to help you figure out the most efficient formula for each particular task.

Excel COUNTIFS function - syntax and usage

As I've 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 2013, Excel 2010, and Excel 2007, so you can use the below examples in any Excel version.

COUNTIFS syntax

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 number, cell reference, text string, expression 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. As well as with any other formula, 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 by a single condition as well as in multiple ranges with multiple conditions.

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.

For the detailed description and syntax of the COUNTIF function, please see this tutorial - Excel COUNTIF function.

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 handle multiple conditions and ranges.

COUNTIFS and COUNTIF (with multiple criteria) for numbers

By and large, COUNTIFS formulas for numbers fall into 2 categories - counting numbers between the two values you specify, and based on several conditions.

Example 1. Counting numbers between X and Y

You can count how many numbers are between the numbers you specify in two ways - using a COUNTIFS function or a difference between two COUNTIF functions.

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

This formula counts how many numbers between 5 and 10 (not including 5 and 10) are contained in cells C2 through C10.
A COUNTIFS formula to count numbers between X and Y

If you want to count cells based on the same condition but including 5 and 10, add "=" to the criteria like this: =COUNTIFS(B2:B10,">=5", B2:B10,"<=10")

COUNTIF formulas to count numbers between X and Y:

  • =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. The formula will produce the same result as you see 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.

Summing up, you use a combination of two COUNTIF functions in the following way. In the 1st function, you count how many numbers are greater than your lower bound value (5 in this example). In the 2nd function, you count how many numbers are greater than the upper bound value (10 in our case). The difference between the first and second number is the result you are looking for.

Example 2. Counting numbers with multiple criteria

Suppose you have a list of products indicating how many items are sold, delivered and in stock. You can use the following formulas to count the numbers of products, at least one item of which has been sold and delivered, i.e. if the values in columns B and D are greater than 0:

=COUNTIFS(B2:B11,">0", D2:D11,">0")
A COUNTIF formula for counting numbers with multiple criteria

Here is one more COUNTIFS formula example with multiple ranges:
=COUNTIFS(B2:B11,"=0", C2:C11,"=0", D2:D11,"=0")

The above formula counts the number of products with zeroes in columns B, C and D. This COUNTIFS formula returns 1 because only Product 5 have "0" in all the three columns.
A COUNTIFS formula for counting numbers in three columns

Note. You do need to use three separate ranges in the formula, one per column, if you want to count the products that contain "0" in all the columns. A simpler formula with a single range =COUNTIFS(B2:D11,"=0") would produce a different result because it returns the total count of cells containing a zero.

Example 3. How to use cell references in COUNTIFS formulas

Of course, you can use a cell reference instead of a number in Excel COUNTIFS formulas. Just remember to enclose the operator in quotes ("") and add an ampersand (&) before the cell reference to construct a text string. For more information about the use of an ampersand in COUNTIF and COUNTIFS formulas, please see Excel COUNTIF - frequently asked questions.

So, let's make a COUNTIFS formula with a cell reference equivalent to the formula from the previous example:

=COUNTIFS(B2:B11,"="&C2, C2:C11,"="&C2, D2:D11,"="&C2)

Cell C2 used in the criteria contains a zero value, so the formula will produce exactly the same result you see in the screenshot above.

Example 4. COUNTIF formulas for non-contiguous ranges

Examples 2 and 3 above demonstrate how to use COUNTIFS in Excel to count cells based on several conditions. Such formulas can be applied both to contiguous or non-contiguous ranges. For example, we have used this formula =COUNTIFS(B2:B11,">0", D2:D11,">0") to find out how many products have a value greater than "0" both in column B and column D.

But what if you want a total count of cells with a certain value in several non-adjacent ranges? For instance, how do you count the total number of zero values in columns B and D? The answer is to use a combination, more precisely, a sum of several COUNTIF functions:

=COUNTIF(B2:B11,"=0") + COUNTIF(C2:C11,"=0")
A COUNTIF formula to count numbers in a non-contiguous range

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

COUNTIFS formulas for text values

In the previous article, I provided an example of Excel COUNTIF formula with two criteria to count the number of cells with 2 different text values in a single range. Now let's see how you can use the COUNTIFS function with multiple criteria and across multiple ranges.

Example 1. COUNTIFS for text values

Suppose, you have a table listing the results of the credits and exams passed by students. What you want to know is how many students have passed all the credits. To do this, you use a usual COUNTIFS formula with several different ranges and the same criteria:

=COUNTIFS(B2:B7,"=P",C2:C7,"=P",D2:D7,"=P",E2:E7,"=P")
A COUNTIFS formula with several different ranges and the same criteria

If you want to know how many credits a particular student has passed or not passed, you can utilize either a COUNTIFS or COUNTIF formula, because this time you need a single range only.

For example, the below formulas will tell you how many credits Bella has passed:

=COUNTIFS(B3:E3,"=P")

=COUNTIF(B3:E3,"=P")
The COUNTIFS formula to count cells with text values in a single range

Example 2. COUNTIFS with wildcard characters

As I mentioned at the beginning of this tutorial, you can use the following wildcard characters in Excel COUNTIFS formulas:

  • 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 the character in your COUNTIFS formula.

Now let's see how you can use a wildcard char in your real COUNTIFS formulas in Excel. Suppose, you a list of projects assigned to your company's employees. You want to find how many projects are already assigned to someone, i.e. any name is stated in column A. And because we are leaning 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: =COUNTIFS(B2:B10,"*",D2:D10,"<>"&""))

Please note, you cannot use a wildcard character criteria with the second range 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.

Important note! Please remember, when you use an Excel COUNTIFS formula with multiple ranges, all of the ranges must all be the same size, i.e. have the same number of rows and columns. Otherwise, your formula will return the #VALUE! error. This applies to all COUNTIFS formulas in Excel, with any data type - numbers, dates and text.

See also

Want to find more inventive tips, tricks and tutorials?

Please follow us on Google+:

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

    • BRAJESH says:

      THANKS VERY MUCH ITS VERY USEFUL FOR ME

  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:

    Hello,

    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.

  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:

    Hi,
    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,

    Glenn

    • 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:

        Hi,

        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:
      =COUNTIFS(A2:A10,"<6",B2:B10,">="&TIME(5,0,0),B2:B10,"<="&TIME(7,0,0))+COUNTIFS(A2:A10,"<6",C2:C10,">="&TIME(5,0,0),C2:C10,"<="&TIME(7,0,0))

      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:
      =COUNTIFS($A$2:$A$8,"F",$B$2:$B$8,"<15")

      F equal 15 pero not more than 19:
      =COUNTIFS($A$2:$A$8,"F",$B$2:$B$8,">=15",$B$2:$B$8,"<19")

      F equal 20 but not more than 24:
      =COUNTIFS($A$2:$A$8,"F",$B$2:$B$8,">=20",$B$2:$B$8,"<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,
        A
        B
        C
        A
        D
        H
        A
        J
        K
        L
        A
        U

        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
            Dhaka

          • Imam+Azad says:

            ANY UPDATE
            Please be Reply

            IMAM

  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.

    Cheers,
    vcoolio.

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

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

    Cheers,
    vcoolio.

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

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

    Cheers

  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:

    Hiya

    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:

    Hi

    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:

    Hi,
    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:

    OR

    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:

    Hi,

    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:

    Hi.

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

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

    Thanks,

    Angelo

  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

    {=SUM(LEN(A$1:E$6)-LEN(SUBSTITUTE(A$1:E$6;B9;"")))/LEN(B9)}
    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:

    Hi,
    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.

  36. Lindsey says:

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

    =COUNTIFS(DailyAtten!C:C,A6,DailyAtten!E:E,"1")

    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:

    =COUNTIFS(DailyAtten!C:C,A6,DailyAtten!E:E,"1")
    =COUNTIFS(DailyAtten!C:C,A6,DailyAtten!F:F,"1")
    =COUNTIFS(DailyAtten!C:C,A6,DailyAtten!G:G,"1")
    =COUNTIFS(DailyAtten!C:C,A6,DailyAtten!H:H,"1")
    =COUNTIFS(DailyAtten!C:C,A6,DailyAtten!I:I,"1")

    But instead, it copies like this:

    =COUNTIFS(DailyAtten!C:C,A6,DailyAtten!E:E,"1")
    =COUNTIFS(DailyAtten!D:D,B6,DailyAtten!F:F,"1")
    =COUNTIFS(DailyAtten!E:E,C6,DailyAtten!G:G,"1")
    =COUNTIFS(DailyAtten!F:F,D6,DailyAtten!H:H,"1")
    =COUNTIFS(DailyAtten!G:G,E6,DailyAtten!I:I,"1")

    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:

    Hello,

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

    WHQKA0,WHQKB0,WHQKC0,WHQKE0,WHQKT0,WHQLC0

    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:
      =OR(D2="WHQKA0",D2="WHQKB0",D2="WHQKC0",D2="WHQKE0",D2="WHQKT0",D2="WHQLC0")

      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?

    Thanks

    Monique

    • 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:
    =SUM(IF(FREQUENCY(MATCH(G2:G7497,G2:G7497,0),MATCH(G2:G7497,G2:G7497,0))>0,1))
    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:
    =COUNTIFS(C:C,"LF",(AF:AF),"")
    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:
      {=SUM((IF(FREQUENCY(MATCH(G2:G7497,G2:G127,0),MATCH(G2:G7497,G2:G7497,0))>0,1,0))*(--(AF2:AF7498<>"")))}

      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

    Thanks
    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 :) )

    DATA:

    LANDLINE NO.
    325163952
    2691919
    4222239
    323445488
    2623224
    324161040
    2314987
    4898389
    2545410
    2735652
    2358818
    4206340
    2382144
    4149143
    4104661
    3463201
    2366113
    322731025
    2694647
    3401031 LOC. 213
    2339450

  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?
    Thanks!

  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:

    Hi,

    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!

    Andrew

  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:
    AA
    AB
    AC....
    BA...
    A1
    A2...
    Could you please give an excel formulae or as a vba macro code to display in an excel.
    Please drop me an email.
    Thanks,
    Rajamani

  48. Bernie says:

    Hi,

    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?

    Thanks,

    Bernie

  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?

    thanks,
    Leo

  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:

    Hello,

    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:

    Hi,

    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:

    Hi,
    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:
      =COUNTIF(B2:F2,"*")

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

      {=SUM(--NOT(ISERROR(DATEVALUE(TEXT(A2:F2,"mm/dd/yyy")))))}

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

  55. Prasad says:

    Hi,

    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.

    Example:

    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.

    Prasad

  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.
    =Countif(ACTL!B3:B50001;A$1;ACTL!$E$2:$E$50000;B$2;ACTL!$G$2:$G$50000;$B7)
    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.
    Utkan

    • 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:

    Hello,
    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!

    Floris

    • 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:

    Hi,

    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.

    Thanx.

    • 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
    Hi,

    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:
          =IF(AND(COUNTIF($A$2:A2,A2)=1,WEEKDAY(A2,2)=6),1,0)

          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:

    Hi,

    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

    thanks

  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:

    Hi,

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

    Regards.

  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:
      =SUMIF(A1:A20,"a",B1:B30)

      Sum values in column B corresponding to "E" in column A:
      =SUMIF(A1:A20,"e",B1:B30)

  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
    =SUMPRODUCT((D:D"")/COUNTIF(D:D,D:D&""))

    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.

  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

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

    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:

    Hi,

    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
    Gavin

  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
    Steve
    Beto
    Frank

    B Ted
    Mike
    Chris
    Nate
    Terry

    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:

    Svetlana,

    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

    City
    Date
    Type
    Transaction

    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:

    hi,
    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

    Regards
    Bhajan lal Sharma

  80. Kit Elloran says:

    Hi,

    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%

    And

    200,000 and Above =2%

    Please help me ...

  82. NYT8er says:

    Greetings,
    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:

    COUNTIFS(Table1[Section],”Section”,Table1[Safety],””)

    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:

    hi,

    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
    CODE LOCATION KM COST EXTRA
    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

    thanks

  84. raed says:

    -----A----------B------------C------------D-----------E
    --CODE------LOCATION-------KM-----------COST--------EXTRA
    --100-------SOHAR---------50------------25-----------2--

  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:

    Hi

    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?

    Regards

  87. raed says:

    as example,,in the first sheet as below,,,
    A B D E F
    CODE FILLING STATION K.M AMOUNT ALAWNS
    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
    CODE FILLING STATION K.M AMOUNT Allowances
    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:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    25
    64
    54
    654
    546
    40

    so how can find highest number in excel sheet

  89. RC says:

    Hi,

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

    Could you help?

    Thanks

    • 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:

      =SUMIF(B:B,"word",A:A)

  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
    A2=Knife
    B2=2
    A3=Spoon
    B3=4
    A4=Fork
    B4=3
    A5=Knife
    B6=7

    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
    =SUMPRODUCT(--(MONTH(J6:K1000)=11)*(YEAR(J6:K1000)=2014))

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

      =SUMIF(B:B,"b",A:A)

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

  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:

    Hi,

    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,
    Dan

    • 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:

    Hi,
    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:

    If a worksheet arranged as mentioned below, HOW CAN COUNT HOW MANY CT BECOME BETWEEN AGE OF 21 TO 25. I HAD TRIED TO CALCULATE THIS IN ANOTHER SHEET BY USING FORMULA =COUNTIFS('NOMINAL ROLL '!A:A,"=CT",'NOMINAL ROLL '!C:C,">=21",'NOMINAL ROLL '!C:C,">=25"). BUT NOT SHOWING ACTUAL RESULT. REQUEST INTIMATE A SUITABLE FORMULA TO SOLVE THE THIS PROBLEM, PLEASE.
    NOMINAL ROLL (SHEET-1)
    A B C
    RANK NAME AGE
    CT Vinod Singh 22
    CT Rajesh Kumar Purohit 24
    CT VIJAY KUMAR 25
    SI CHANDAN KR PASWAN 25
    CT MANTU KUMAR RAM 23
    CT MITHILESH KUMAR JH 24
    CT PURUSHOTAM KUMAR 23
    CT AJAY MANDAL 27
    SI KAILASH SOREN 29
    CT DEBASHIS SHIL 27
    SI CHHOTU UJMODAK 30
    CT DIPAK TIGGA 27
    CT SUMANTA KR DAS 30
    CT SAGAR DHAMALA 28
    HC SANDEEP KUMAR 25
    CT RAYAZ AHMAD 23
    CT MONIRATH MONDAL 23
    CT BABLU NAIK 23
    HC PRADIP MONDAL 23
    CT TAPASA ROY 25

  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

    IF IT IS NOT POSSIBLE TO USE NUMBERS IN SINGLE CELL
    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)

    Example
    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

    regards
    wayne

  106. Wayne says:

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

  107. Chalinda K says:

    Hi,

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

  108. Bil says:

    Hi,

    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:

    Hello,

    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?

    7:31:11
    7:48:41
    7:51:35
    7:59:49
    8:02:01
    8:03:47
    8:06:41
    8:07:16
    8:07:47
    8:15:20
    8:19:43
    8:29:16
    8:33:42
    9:09:05
    9:09:36
    9:11:56
    9:19:27
    9:19:55
    9:20:25
    9:26:13
    9:34:33
    9:35:19
    9:47:05
    9:49:42
    10:10:10
    10:10:24
    10:10:37
    10:10:59
    10:11:10
    10:11:22
    10:11:43
    10:12:07
    10:12:29
    10:12:42
    10:13:04
    10:13:25
    10:14:05

  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:

    Hi,

    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:

    Hello

    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

    thanks

  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.
    =COUNTIF(A3:A25,32")
    =COUNTIF(A3:A25,32)
    =COUNTIF(A3:A25,"32")

    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:

    HI
    I PREPARED A WORKSHEET OF OUR EMPLOYEES TO CALCULATE SOME DATA SUCH AS THERE AGE AS ON DATE, DIVIDED THEIR AGE GROUPS ETC. BY GIVING DATE OF BIRTH. I CALCULATED THEIR AGE BY APPLYING =YEARFRAC(A1,TODAY(), 1) FORMULA. FURTHER I TRIED THEIR AGE BY DIFFERENT GROUPS SUCH AS 20-25, 26-30 ETC. FOR WHICH I APPLIED =COUNTIFS(C:C,"=CT",'G:G,">=21",G:G,"<=25"). BUT DID NOT SHOWING ACTUAL NUMBERS OF PERSONNEL COMES UNDER THAT CATEGORY IN SAID WORK SHEET. REQUEST HELP ME TO SOLVE THIS ISSUE.
    S/N RANK NAME DATE OF BIRTH AGE AGE 21-25

    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

    ACTUALLY ALL ARE COMES UNDER 21-25 AGE GROUP BUT SHOWING ONLY 17 NOS.

    THANKING U

  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?

    Thanks
    Ben

  126. Chandan Tiwar says:

    hi,

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

  128. May says:

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

    Col1
    ab
    cd
    ef
    ef
    cd
    ab

    Result
    ab-2014-01
    ab-2014-02
    cd-2014-01
    cd-2014-02
    ef-2014-01
    ef-2014-02

  129. musharaf ahmed says:

    I have following data sheets.

    sheet1data
    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.
    .22
    .45
    .67
    .31
    Etc.

  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:

    Hi,

    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.

    B
    null
    null
    null
    A
    B
    A
    A
    null
    A
    null
    A
    B

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

  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.

    E.G

    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:

    =COUNTIF(X4:X41,5)+COUNTIF(X4:X41,10)+COUNTIF(X4:X41,15)

    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?
    ie
    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?

    =COUNTIF(K15,"<13")*SUM(J15/52*4,(J15/52*K15))

    Any help would be appreciated

  145. Rasel says:

    Hi,
    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:

    Hi,
    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:

    =COUNTIF(X4:X41,5)+COUNTIF(X4:X41,10)+COUNTIF(X4:X41,15)

    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:

    Hi,

    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?

    Thanks!

  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:

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

    =COUNTIFS('Sheet1'!$AV:$AV,""&"",'Sheet1'!$AR:$AR,"*Feb*") +
    COUNTIFS('Sheet2'!$AV:$AV,""&"",'Sheet2'!$AN:$AN,"*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)
    1106
    2104
    1301
    1303
    1402
    2201
    2309
    1205

    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...
    Hi!

    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:
      =COUNTIFS(Infrastructure!$A$2:$A$2000,"Infrastructure",Infrastructure!$C$2:$C$2000,">="&(TODAY()-11))

  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#

    =COUNTIFS(A1:A6,"Apple",B1:B6,"Inventory")

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

    Help!!

  172. hemant sharma says:

    Thanks for Guide.

  173. hemant sharma says:

    Svetlana Cheusheva you are so beautiful

  174. Elvis says:

    Hi,

    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
    RW CS RW CS RW CS RW CS RW CS
    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.

    Regards.
    Joseph

  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:

    Hi,
    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.
    Jessie

    Warm regards,
    Jessie

  182. sue says:

    Hi,
    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?

    Thanks

    Sue

  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?

    EDU
    HS
    BA
    BS
    AA
    AS
    HS
    HS
    BA
    AA

  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:

    1 JAMES SHJ MALE MALE FEMALE KIDS
    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
    5 JOSEPH AUH MALE 11
    6 ZARINA SHJ FEMALE
    7 WINSTON DXB MALE
    8 JENNIFER SHJ FEMALE
    9 MINNU SHJ KIDS
    10 ARASH AUH MALE
    11 MAHIR SHJ MALE

  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.
    eg.
    1
    4,5
    6
    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):
      =SUM(LEN(A2:A10)-LEN(SUBSTITUTE(A2:A10,",",""))+1)

      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.

    Thanks

  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
    A
    B
    C
    D
    E
    f

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

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

  200. Lea says:

    Hi!

    How can I use countifs and vlookup together?

    Thank you.

  201. Randhir Godase says:

    If
    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:

    Hi,

    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(ISERROR(VLOOKUP($C2,$A$2:$A$9,1,0)),"",$C2&VLOOKUP($C2,$A$2:$B$9,2,0))

      If you only want the figure then...
      =IF(ISERROR(VLOOKUP($C2,$A$2:$A$9,1,0)),"",VLOOKUP($C2,$A$2:$B$9,2,0))

  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?
    =COUNTIFS($A$25:$A$5000;"01";$G$25:$G$5000;"D";$I$25:$I$5000;"S";$L$25:$L$5000;"EC"&"")

    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:

    Hello,

    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:

    Hi,

    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:

    Hello!
    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.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite for Excel Professionals
 
 
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard