Comments on: 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. Continue reading →

Comments page 38. Total comments: 2055

  1. I am trying to figure out how many school buses we have on the road at a given time (every 15 minutes, 5:45 to 5:59, 6:00 to 6:14, 6:15 to 6:29, all the way to 9:00 PM etc).

    Column A is the route #,Column B is the Start Time, and Column is the End Time and Column D is the duration of the entire route.

    RRoute # End Time Start Time Duration
    762 AM1 7:25 AM 5:46 AM 1:39
    792 AM1 7:24 AM 5:58 AM 1:26
    764 AM1 7:25 AM 5:59 AM 1:26
    716 AM1 7:25 AM 6:07 AM 1:18
    794 AM1 7:20 AM 6:10 AM 1:10
    715 AM1 7:25 AM 6:12 AM 1:13
    754 AM1 7:20 AM 6:17 AM 1:03
    706 AM1 7:25 AM 6:17 AM 1:08
    766 AM1 7:18 AM 6:21 AM 0:57
    767 AM1 7:23 AM 6:23 AM 1:00
    770 AM1 8:20 AM 6:23 AM 1:57
    798 AM1 7:15 AM 6:24 AM 0:51
    763 AM1 7:25 AM 6:28 AM 0:57

    I have been able to get a formula to work using the Start Time, but I can't get it right to keep counting that bus all the way to the end time.

    Thank you.

  2. I need some help, I'm tying to use a countifs function. Let's say that I want to add "Matt" and "Bella"(column A), then continue the formula "Biology" (column D). I can't seemed to make it work.

  3. I am trying to work with the countifs formula to come up with the following.
    Between date range 1/3/2016 and 1/10/2016 how many of this list are Class A along with how many are for this particular area (GA).

    My current formula is:
    =COUNTIFS(DATE,">="&$B$2,DATE,"<="&$B$28,Class,"A")

  4. WE WANT TO APPLY 70 IF CONDITIONS LIKE
    BUT NORMALLY AAPLY ONLY 9 CONDITIONS, PLEASE HELP FOR APPLY 70-80 IF CONDITION, IN THIS FILE WE HAVE 2 SHEET INVENTRYDTA LINK AND OTHER SHEET1 SO APPLY IF ON SHEET 1 AND THAT WILL FETCH DATA FROM INVENTRYDTA LINK.
    BUT WE WANT APPLY 70 IF ON SHEET 1

    =IF(C18=INVENTRYDATA LINK'!B2,INVENTRYDATA LINK'!D2,IF(C18=INVENTRYDATA LINK'!B3,INVENTRYDATA LINK'!D3,IF(C18=INVENTRYDATA LINK'!B4,INVENTRYDATA LINK'!D4,IF(C18=INVENTRYDATA LINK'!B5,INVENTRYDATA LINK'!D5,IF(C18=INVENTRYDATA LINK'!B6,INVENTRYDATA LINK'!D6,IF(C18=INVENTRYDATA LINK'!B7,INVENTRYDATA LINK'!D7,IF(C18=INVENTRYDATA LINK'!B8,INVENTRYDATA LINK'!D8,IF(C18=INVENTRYDATA LINK'!B9,INVENTRYDATA LINK'!D9,))))))))

  5. can i count different date value in summery next sheet

  6. this is what I would like to accomplish. I have a list of words. This list of words could be long but for simplicity, let's say I am looking for any occurrence of orange apple or banana. Any time any of those words show up, I would like to get a count for each occurrence.

    I would like you to get me some bananas, strawberries, oranges, grapes and kiwis.

    I would like for the logic to recongnize that there were 3 occurrences and bring back the number 3. The logic may be in one field or it may be in multiple fields.

    Any thoughts.

    1. Hello Chuck!
      You can calculate apples, bananas and oranges separately, and then sum the results. For example =COUNTIF(D1:D13,"apple")+COUNTIF(D1:D13,"orange")+COUNTIF(D1:D13,"banana")

    2. try COUNTIF(Range,"*banana*") to count all occurrence of banana from the Range

  7. HI,

    I have send you an email with the excel sheet attached to it. Please do consider it and help me on that.

    Country China Sweden India Japan Korea *Can be inc or dec
    Fruits Apple Banana Grapes Pineapple Cherry *Can be inc or dec
    Month January March June July *Can be inc or dec

    I need to verify the total number of consumption by each country in each month
    Data can be increased or decreased wrt to the requirements. Status must be accepted only

    Status>> accepted
    Total count Country wise>>
    Total count Fruits wise

  8. Hi,

    Within Cells A33:A151, I have options of saying either "LISTING" or "DBL END".

    In cell N152, my current formula is as follows:
    =AVERAGEIF(A33:A151,"LISTING",N33:N151)

    This formula works, however, I am having trouble writing the formula so that when a cell within A33:A151 says "DBL END" (as opposed to "LISTING"), Cell N152 still calculates accordingly.

    Thanks.

  9. Hi,

    please how can I write a formula with a series of range and each range has a number that should be added.E.g Range 1-500,50 should be added,range 501-1000, 100 should be added and so on.

    Thanks.

  10. Hi

    Is there a way I can count cells with the same conditional formatting? For example, I have cells that will turn green if they meet certain criteria and the others will not be green and I would like to count the cells in a column that are green. Is there a way to do that?

    Thanks in advance.

  11. hello there,
    A1 CONTAIN PACKAGE NUMBER 123456
    B1 CONTAIN AMOUNT 100$
    C1 CONTAIN STATUS DELIVERED OR NOT DELIVERED

    HOW CAN I COUNT THE AMOUNT FOR ONLY WORD DELIVERED OR NOT DELIVERED

    THANKS

  12. Hi,
    can you plz provide me solution for this.

    Required Output - Raw Data in Yellow, I need the total count of CRC in Column H7 after selecting the date from drop down in Column F6. The data shuld be changed according to the date from drop down in Column F6.
    1-Apr-15 (SVC)
    1-Apr-15 (CRC)
    1-Apr-15 (CRC)
    1-Apr-15 (CRC)
    1-Apr-15 (CRC)

  13. Hi,
    I am having problem to make formula:
    Subject wise Percentage

    I want to generate formula for boys and girls who passed and failed.

    Subject wise Percentage
    Subjects Pass Fail
    B G T B G T
    Dzongkha 4 26 30
    English 4 8
    Mathematics 4 4 8
    EVS 4 8

  14. Hi, i have the first column which is a list of different people, but the names repeat going down for every new client shown in the 2nd column.

    In a 3rd column there is a list of comments for each different client.

    This column has rows of cells that either have a comment or they are blank.

    What i need help with is counting the # of blank cells in the 3rd column and breaking that total into subtotals based on each unique person from the 1st column.

    So if you have Ashley Gomez(example) occupying 10 rows (1st column) and only 3 of her comment fields have any text in them. It will return the number of blank rows as 7.

    And then the same for Bibi Martinez who occupies 15 rows but has 6 actual comments this value would return as 9 blank

    And so on down through Zoe...

    Hashing this out in my head seems easy enough but the translation to excel logic just escapes me...

    Any help would be very much appreciated.

    -chris

  15. Hi Ablebits Team, I've found myself in trouble trying to work out a way to avoid using Countifs in the following way:

    =COUNTIFS(s!$A:$A,D$1&"*",s!$G:$G,"9318018109402",s!$Q:$Q,">=0")
    +COUNTIFS(s!$A:$A,D$1&"*",s!$G:$G,"9318018109396",s!$Q:$Q,">=0")
    +COUNTIFS(s!$A:$A,D$1&"*",s!$G:$G,"9318018106869",s!$Q:$Q,">=0")
    +COUNTIFS(s!$A:$A,D$1&"*",s!$G:$G,"9318018106852",s!$Q:$Q,">=0")
    +COUNTIFS(s!$A:$A,D$1&"*",s!$G:$G,"9318018106876",s!$Q:$Q,">=0")
    +COUNTIFS(s!$A:$A,D$1&"*",s!$G:$G,"9318018106845",s!$Q:$Q,">=0")
    +COUNTIFS(s!$A:$A,D$1&"*",s!$G:$G,"9318018106685",s!$Q:$Q,">=0")
    +COUNTIFS(s!$A:$A,D$1&"*",s!$G:$G,"9318018106937",s!$Q:$Q,">=0")
    +COUNTIFS(s!$A:$A,D$1&"*",s!$G:$G,"9318018106753",s!$Q:$Q,">=0")
    +COUNTIFS(s!$A:$A,D$1&"*",s!$G:$G,"9318018106920",s!$Q:$Q,">=0")
    +COUNTIFS(s!$A:$A,D$1&"*",s!$G:$G,"9318018112129",s!$Q:$Q,">=0")
    +COUNTIFS(s!$A:$A,D$1&"*",s!$G:$G,"9318018112112",s!$Q:$Q,">=0")
    +COUNTIFS(s!$A:$A,D$1&"*",s!$G:$G,"9318018112136",s!$Q:$Q,">=0")
    +COUNTIFS(s!$A:$A,D$1&"*",s!$G:$G,"9318018118763",s!$Q:$Q,">=0")
    +COUNTIFS(s!$A:$A,D$1&"*",s!$G:$G,"9318018118749",s!$Q:$Q,">=0")
    +COUNTIFS(s!$A:$A,D$1&"*",s!$G:$G,"9318018118725",s!$Q:$Q,">=0")
    +COUNTIFS(s!$A:$A,D$1&"*",s!$G:$G,"9318018118756",s!$Q:$Q,">=0")
    +COUNTIFS(s!$A:$A,D$1&"*",s!$G:$G,"9318018118787",s!$Q:$Q,">=0")
    Huge etcetera…

    Basically I get those barcodes from column G in a tab called “S” and each week I need to add new barcodes to the formula but adding a new line is not ideal as I will potentially reach the limit of characters that can be used per formula per cell. My question is, could I possibly combine Countifs with vlookup or get Countifs to get those barcodes from a specified range of cells where I could easily dump more barcodes?

    Thanks heaps in advance!

  16. I want count Number of Male which is 3 Age from X Unit like below table
    Name of Unit Age Gender
    X 3 Male
    Y 4 Female
    Z 5 Female

    Help me

  17. Hi

    I am looking at trying to differentiate between cases and enquiries and count what stage they are at:

    eg

    Case Withdrawn
    Case Withdrawn
    Case Withdrawn
    Case Withdrawn
    Case Withdrawn
    Enquiry Withdrawn
    Enquiry Withdrawn
    Case Closed
    Case Closed
    Case Closed
    Case Closed
    Case Closed
    Case Closed

    I am looking to count how many cases are withdrawn, how many enquiries are withdrawn etc

    Thanks

  18. Thanks that worked. I had a named range of the entire data set and that did not work. Breaking the specific ranges looks to be required.

  19. I have a data set that lists a persons age in years along with their sex. I want to be able to count the number of people who are say Male and ages from 4-19.
    Any suggestions to a formula? I have been able to use COUNTIF to get all persons in that age but unable to now further filter by sex. Trying COUNTIFS but not able to get the desired result.

    suggestions?

    1. @Merlyn, Please check this result.

      A B
      Age Sex
      3 Male
      4 Female
      2 Male
      6 Female
      9 Male
      22 Female
      11 Male
      21 Female
      10 Male
      7 Female

      COUNTIFS(A2:A11,">=4",A2:A11,"<=19",B2:B11,"Male")

      This should give you result for age between 4 and 19, and,
      all Males.

  20. How to use ~ in criteria using COUNTIFS?

  21. I am having values in column E like CIVIL, MECHANICAL, ELECTRICAL, INSTRUMENT..... In column O I am having values like 0:30, 0:25,0:45,0:51,0:48,1:21 (all are time format)like that. I need a formula to count number of CIVIL is more than 0:30 minutes.

    Please help

  22. =SUMIF($J$8:$J$40,$AK7,S8:T40)this is posible

  23. Having Issues with a Count IF, I was wondering if you could help. I want to Count if there is date in Cell and if another column reads not released.

    =COUNTIF('Development '!E3:E52,"",'Development '!E3:E100","Not Released")

    1. Hi Sean,

      Try the following formula:

      =COUNTIF('Development '!E3:E52,"")+COUNTIF('Development '!E3:E100","Not Released")

  24. Hi,

    May I have support from you guys,
    this function is not working: =COUNTIFS(C:C,"=CW17",J8:L103,"=Customer not available")!!!!!!!!!!
    C:C= is a criteria range contain Calendar weeks
    J8:L103= I need to count a criteria " Customer not available"
    when I Enter that function, I get this answer= #Value!

    Please please assist me.

    Best.

  25. Hi, I wish to count the number of cells that say "yes" in column A relating to the date between a certain range, ie: 01/10/2015 to the 31/10/2015 in column B so for example if I have 10 people who say yes in Oct on specific dates in October, it would read 10 in the results call. I am currently using this formula:

    =COUNTIFS(G5:G12,"=Nov",H5:H12,"=Yes")

    but I don't know how to pick up the result then reading cells with specific dates, ie: 20/11/2015. I want to pick up the result between the 1st Nov and the 3th Nov.
    Can anyone help please?

  26. Hi
    Value in cell A3 is 10
    I want to check this value tn 4 Different Criteria
    1)Less than 20
    2)Greater than 20 but less than 50
    3)Greater than 50 but less than 80
    4)greater than 80
    Please help
    Regards
    Venkat

  27. I have 2 columns of data. I would like to count the number of cells where column 1 is greater than column 2. I imagined something like this: =COUNTIF(G17:G50,">C7:C38") But that does not work. Suggestions? Thanks.

    1. Hello Dave,

      To compare ranges, you can use an array Sum formula similar to this (completed by pressing Ctrl+Shift+Enter):

      =SUM(--(G17:G50>C17:C50))

      Please pay attention that the ranges should be of the same size, i.e. include the same number of rows.

  28. what is the formula if I want to get the total number of invoice issued per saleperson and categories by date, like from Jan, Feb, March etc. for the whole year.

    thank you!

  29. I have cells with multiple data separated by commas, How to use countif or countifs fuction on this data. Like the student name has multiple student1,student 2etc separted by commas. How to use countif on them.

    Lecturer Name Subject Student Names
    Dr Raj Gross Anatomy Lecture Student 1, Student 3,
    Dr Sreekanth Gross Anatomy Lecture Student 1, Student 2, Student 3, Student 5, Student 6
    Dr Hannah Histology Lecture Student 1
    Dr Stella Anatomy Lab Student 1, Student 2, Student 3, Student 4, Student 5, Student 6

    1. Please I am on this table too.
      Mine is about cells with multiple years entered n separated by commas.

      How do I count cells that contain only two of the many years entered in no particular order?

  30. Dear Svetlana

    I have cells with multiple data separated by commas, How to use countif or countifs fuction on this data. Like the student name has multiple student1,student 2etc separted by commas. How to use countif on them.

    Lecturer Name Subject Student Names
    Dr Raj Gross Anatomy Lecture Student 1, Student 3,
    Dr Sreekanth Gross Anatomy Lecture Student 1, Student 2, Student 3, Student 5, Student 6
    Dr Hannah Histology Lecture Student 1
    Dr Stella Anatomy Lab Student 1, Student 2, Student 3, Student 4, Student 5, Student 6

  31. what is the formula if I want to get the number of invoice issued per saleperson and categories by date, like from Jan, Feb, March etc. the whole year.

    thank you!

  32. how do I add numbers if a certain product is beside it

    a1 b1
    200 161
    300 NC
    900 2100

    I want to be able to automatically add a1 if the square contains one of the products in B1 -how do I do that?

  33. Dear Svetlana Cheusheva,

    I had emailed you already.
    I have attached a .jpeg worksheet in this comment with vertical data in it. In the column PRODUCT ID, there is different numbers start with CL00 and they repeat multiple times in the same column on the same sheet and also on the different sheet of the same file.I want to count them in a new sheet and also want the sum of consumption column in the same row.

    please help
    I have set an example in the attachment by manually.

    Sno. Orderid OrderDate BOM Productid Specid Consumption
    1 564084 4/3/2016 2 CL0039911 944518 3.2
    2 564084 4/3/2016 0.5 CL0038981 944519 3
    3 564091 4/3/2016 0.5 CL0038981 944534 3
    4 564093 4/3/2016 0.5 CL0038981 944536 3
    5 564099 4/3/2016 2 CL0039911 944545 3.2

    I have more than 30 sheets of the same data but with different consumption how to count them in like I said before.

  34. Dear Svetlana Cheusheva,

    I had emailed you already.
    I have attached a .jpeg worksheet in this comment with vertical data in it. In the column PRODUCT ID, there is different numbers start with CL00 and they repeat multiple times in the same column on the same sheet and also on the different sheet of the same file.I want to count them in a new sheet and also want the sum of consumption column in the same row.

    please help
    I have set an example in the attachment by manually.

  35. Hi Renat!
    Many thanks for your great help.
    I have this formula posted by Jeff at Excel University Website. It works very fine for me and i found helpful since it uses columns header and structured data reference.Above all the formula is very reliable and consistent.However, I tried to copy modify same formula in another cell to provide also for countifs function i.e to calculate the number of value items added in the SUMIFS function by simply replacing the word " SUMIFS" in the formula with "COUNTIFS" but it doesn't work. Need your help with this, if u don't mind.

    =SUMIFS(INDEX(tbl1,,MATCH(D$7,tbl1[#Headers],0)),tbl1[[Product]:[Product]],$B8)
    Thank you in advance
    Monas

  36. Quantity pcs/ctn Carton to be created Left over Quantity
    725 30 24 5

    What will be the formula used for "Carton to be created " and "Left over Quantity"?

  37. Hi,

    I have 2 columns, say equipment model & location name . I need to count the location name by the equipment model.

    model location
    aa 1a
    bb 2b
    cc 3c
    aa 4d
    cc 1a
    dd 2b
    bb 1a
    bb 2b

    I require the result as count of 1a in aa = 1, 1a in bb = 1, 2b in bb = 2. can you help me with the correct formula.

  38. I am trying to use CountIF to determine from a data dump the number a user has from different categories of work. For example Column A has various names, and column D has a category of Expenses, and Column F has Food as a category.

    I can't seem to get this to work it keeps giving me an error and the the totals I want are how many Barbara completed from Each category, then How many Sandra did from each category. The totals will be on a different worksheet in the workbook than where the action data is sort of a summary sheet.

    Can anyone please help?

  39. Hello,
    My data set consist of several sales entries like; date, rep name,location,region,item and if sales was done at POS which is depicted with a "Y" or an "N". In my analysis, i need to show the number times the letter "Y" appears for each sales person using entry date and reps name.
    E.g =countifs(b2:b50,12/03/16,D2:D50,JOHN,H2:H50,"Y").

    Regards

    1. Hi Kingsley Odu,

      Please try the following formula:
      =COUNTIFS(B1:B50,"12/03/16",D2:D50,"JOHN",H2:H50,"Y")

  40. I currently have a yes/no spreadsheet with approx 30 individual questions listed down the cells. 12 of these questions are mandatory and have a 0.3666 percentage weighting allocated to each (Total for all mandatory questions is 44%). The non-mandatory questions also have different percentages allocated to them totaling up to 56%.
    If a client does not answer one of the mandatory questions, then the spreadsheet should show 'FAIL' against that company, but because of the percentage number against the other questions, the result is showing #value!
    How do I get this field to say FAIL (text) if one of the mandatory field has 'no' in it and show a percentage (number) if all mandatory have 'yes' against them

    Currently have -
    =IF(COUNTIFS(R6C6:R43C6,"=Mandatory",R[2]C:R[39]C,"=No")>0,"fail",COUNTIFS(R6C6:R43C6,"=Mandatory",R[2]C:R[39]C,"=Yes")*"0.03666")+(COUNTIFS(R6C6:R43C6,1.5%,R[2]C:R[39]C,"=Yes")*0.015)+(COUNTIFS(R6C6:R43C6,3%,R[2]C:R[39]C,"=Yes")*0.03)+(COUNTIFS(R6C6:R43C6,4%,R[2]C:R[39]C,"=Yes")*0.04)

    1. Hi Charmaine Barrett,

      To help you better, we need a sample table with your data in Excel and the result you want to get. You can email it to support@ablebits.com. Please add the link to this article and your comment number.

  41. I looking for a formula which could meet multiple criterias. I looking for a specific manufacturer (say dell) on first column, if the condition meets, i'm looking for specific model (say dell lattitude 630) on second column, if the condition meets, i want to count the number of windows 7 machines which is present on the third column. I'm using sumproduct to total across the sheets for one criteria, now i want to use it for multiple criteria. We use filter to filter column by column to get that. Please help me.

    1. Hi Jayakumar Krishnamoorthy,

      Please try the following formula:
      =COUNTIFS(A1:A10, "dell", B1:B10, "dell lattitude 630", C1:C10, "windows 7")
      where
      manufacturer values are in A1:A10
      model values are in B1:B10
      os version values are in C1:C10

  42. How do I count from non-adjacent cells with multiple criterias?
    I am working on our schedule per week, I want to know the headcount present every four hours in a day including the overtime (criterias are advance, extend, training, blank etc.)I used the countif formula but it is so long due to multiple criterias and non adjacent cells.

  43. Hello,
    Can someone please help me with this?
    I'm looking for a way to count the occurrences in a column where two or more consecutive values are grater than 5.
    for example:

    Column A
    Row 1 0.61
    Row 2 0.62
    Row 3 5.12
    Row 4 6.34
    Row 5 3.58
    Row 6 5.8
    Row 7 0.62
    Row 8 13.62
    Row 9 5.09
    Row 10 7.65
    Row 11 0.61

    In this example the result from column A would be two (2). A3 and A4 is one and A8-A10 is another. Row 6 is not counted because the value right before or after is less than 5.

  44. Please all Excel Formula Example Send me

    Thanks

    Regards

    Mangalsingh
    9202267674

  45. Hi,
    I have Date and times for different days, eg
    3/6/2016 10:35
    3/6/2016 10:50
    3/6/2016 10:59
    3/7/2016 11:45
    3/7/2016 11:50
    3/7/2016 11:53
    3/8/2016 9:09
    3/8/2016 9:27
    3/8/2016 9:56
    3/8/2016 9:57.
    All This is Row A. in Cells
    C1 I have 3/6/2016
    C2 I have 3/7/2016 and in cell
    C3 Ihave 3/7/2016.
    in D1, I want to Say " countif,range A:A contents a date like in C1".
    i.e, count the cells in range A:A that contains the dates without time.
    How can I possibly do this??

    1. Hi Gladious Mbah,

      Please try the following formula:
      =COUNTIF(A1:A15, "<>*:*")
      where the date and times are in A1:A15.

  46. I have a range of Data from sap that I need to organise into work areas by centre.

    I have used COUNTIFS to gather one area work orders by work center which looks like:

    =COUNTIF('RAW DATA 1 LAST WEEKS Orders '!M7:M4394,"3001-320-3523*")+COUNTIF('RAW DATA 1 LAST WEEKS Orders '!M7:M4394,"3001-320-3563*")+COUNTIF('RAW DATA 1 LAST WEEKS Orders '!M7:M4394,"3001-320-3516*")

    Now I need to count the work orders that belong in the formula above, but also have *TECO* in the J column of the same page?

    Could you please help with this?

    Thanks in advance.

    1. You should use the array formula to solve this task.
      For example:
      {=SUM(1 * (LEFT('RAW DATA 1 LAST WEEKS Orders '!M7:M4394, LEN("3001-320-3523"))="3001-320-3523") * (IF(ISERROR(FIND("TECO", 'RAW DATA 1 LAST WEEKS Orders '!J7:J4394)), 0, 1)))}
      To enter this formula press CTRL+SHIFT+ENTER.

  47. Please I have range with many numbers i want to count with excluding any cell starting with 6.

    Regards,

  48. Hi ,
    I have one situation where I am stuck. I have two columns, one with car name and 2nd with number of sales in a year.
    Can i use count if to get what car and how many units are sold.
    I tried giving criteria as Camary and range as the no of units sold, but that did not work. I tried selecting both columns as range, but that did not work either.
    Please help

    1. Hi Gurpreet,

      You can use the following array formula:
      {=SUM(B1:B10 * (A1:A10 = "Camary"))}
      where
      "car name" values are in A1:A10
      "number of sales" values are in B1:B10
      To enter this formula press CTRL+SHIFT+ENTER.

  49. Hi

    i use this formula =COUNTIFS('Process Data'!$A:$A,Tally!$A3,'Process Data'!$C:$C,Tally!$B3,'Process Data'!$H:$H,Tally!C$2)

    I need to use in VBA how to use Countifs Function so please tell me code foe this

  50. Hi, Can you help me in generating a countif formula in my table? For example Cell-A states the Company Names, ex. A,B,C,D, then in Cell-B states the specific date they submit their reports, ex. 01/31/2015,02/12/206. The problem is I want to count the numbers of the report they submitted in a month. Many companies, Many reports submitted in 1 month, How can I use countif in this problem? please help me. I dont know how to use date in Countif.

    1. Hi Gab,

      You can use the following formula to count reports, say, submitted in January 2016:

      =SUMPRODUCT(--(MONTH(B2:B100)=1), --(YEAR(B2:B100)=2016))

      Where B1:B100 are cells containing submission dates.

      To count reports for any other month / year, simply replace =1 and =2016 in the formula with the required numbers.

Post a comment



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