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 22. Total comments: 2074

  1. Hi how can a get a total count for every month thru out the year for example if a user has a yes or no value in a cell i want to get a total of all the yes in that year and show the month where the yes occured?

  2. Check In Check Out Guest Name Room Category Room Booked
    02-Dec-19 03-Dec-19 A Deluxe 3
    02-Dec-19 05-Dec-19 B Super Deluxe 1
    06-Dec-19 08-Dec-19 C Deluxe 1
    06-Dec-19 07-Dec-19 D Super Deluxe 2
    i want to know how many "deluxe" booked on 2dec,3dec... as so on? and same for other room category.
    Thanks

  3. HI
    PLEASE HELP WITH THIS TABLE
    X/Y VALUES
    X 9
    X 23
    Y 13
    Y 7
    X 29
    X 19

    WHAT I WANT IS
    COUNT OF X
    1. LESS 10
    2. BETWEEN 11-15
    3. BETWEEN 16-20
    4. BETWEEN 21-25
    AND ABOVE 25.

    WHAT FORMULA SHOULD I USE?
    HELP

  4. I have doubt which formula to apply to find the difference in clumn two with the repeated seqence, as below, A1 781
    A1 781
    A1 781
    A1 781
    A1 800
    A1 800
    A1 801
    A1 802
    A2 410
    A2 410
    A2 410
    A2 410
    A3 500
    A3 500
    A3 501
    A3 510
    A3 510
    A3 511
    A3 511

    Need to find the formula which highlight high difference 2nd column as
    A1 781
    A1 800

    A3 501
    A3 510

    Please help

  5. Hi!
    I am looking for some help with developing a formula. I coordinate a group of volunteers doing once a week service. I keep a schedule of 3 columns, date, the men working, and the ladies working. The schedule is multiple years long. The facility we work in has laid down a new rule that you have to come in at least twice in the past year to be considered current on your status. I have been trying to develop a formula that will reference the schedule sheet and tell me if each volunteer is current on their status, based first on the date (based on today), and next based on if they have met the required two times of working within that year. Can you help? I have tried vlookup, countifs, countif, as well as a long combination of ifs, ands, and other things, all of this to no avail.

  6. I have a COUNTIFS formula to calculate the number of concurrent license usages (in this months data)like this =COUNTIFS(F$2:F$2468,"="&F2). In another column (N) I have the application name that used the license. If I sort my sheet on the application then the start time Col F could I expand the formula to count the concurrent usage for each application (I might have 10 different applications and the usage might vary in a particular month from 1 row to 1000 (or more).
    Thanks in hope.
    Gary

    1. I just realised the formula got truncated when I pasted it in. This is my current formula =COUNTIFS(F$2:F$3111,"="&F2.

      1. add this to the end of the formula ,G$2:G$3111,">="&F2)

  7. Hi There
    i got 3 number in one cell which mean that i'm using Alt + Enter to add the number. My question is how do i sum the total of that 3 number in empty?

  8. Dear Sir,
    i am facing problem from couple of days
    my problem.... how to set duplication formula on numbering column such as i don't want that someone entry the same number in the same column incase anyone try to enter the same number so the excel warning to them

    i am waiting for your kind response ...Thanks

  9. I need to count number of returning users. I have one column for names and one column for dates. I wanna count if user x visited the site more than 1 time on different dates. Is there a formula for this?

  10. Thanks
    Try this....
    =SUM(COUNTIFS('Dept'!$H:$H,">0",'Dept'!$E:$E,B169,'Dept'!$D:$D,{"Day Care","Inpatient"}))

  11. sir, i need an equation for select and copy the last active column from group of column in excel to another column.

  12. Good Morning everyone,
    I wonder if someone can help. I am trying to count a word within the next 90 days.
    Two tabs, one is called Reporting, where the info is being pulled, the 2nd tab is where the formula will go.
    At the moment, I have a formula of
    =countifs(reporting!$B:$B,"*Business Fulfilment Leeds*",Reporting!$F:$F,"<"&TODAY()+90)
    Although it does seem to count, I know its not producing the correct figures.
    would it be easier if I put in todays date in one cell?
    So, Coloumn B is where Business FL is located and in column F is future dates.
    Any help would be greatly appreciated.
    Thanks

  13. I have refernce names Units, Version and mode defined on my spreadsheet.
    and I wnat to run countifs to get two peices of information the count of how many version 100's per unit
    then a count of legacy's per unit for two different charts. suning the same spreadsheet
    I have useed a countifs statement in my spreadsheet to get a count of (units,"*",version,100) good output
    (units,"A",version,100) good output
    (units,"B",version,100) good output
    (units,"C",version,100) good output
    I tried to use another countifs in my spreadsheet to get (units,"*",mode,"Legacy")
    (units,"*",mode,"Legacy") outputs !value
    (units,"A",mode,"Legacy") outputs !value
    (units,"B",mode,"Legacy") outputs !value
    (units,"C",mode,"Legacy") outputs !value
    But i keep getting !Value for the second set of Countifs.
    is there a limit on the cirtera and howm any CountIfs per workbook?

  14. Looking for a formula to do each separate entry below

    count of odd numbers below 29 inclusive in row
    count of even numbers below 30 inclusive in row
    count of odd numbers above 31 inclusive in row
    count of even numbers above 32 inclusive in row

  15. I have two lists . If LIST B IS ONLY APPLE FOR LIST A ID'S THE COUNT SHOULD BE 1. IF LIST B HAS OTHER THAN APPLE or APPLE FOR LIST A ID'S THAT SHOULD NOT BE COUNTED. PLEASE HELP

    List A List B COUNt OF ONLY APPLE FOR LIST A ID's ARE : 4
    111 APPLE
    111 APPLE
    111 APPLE
    111 APPLE
    112 APPLE
    112 ORANGE
    112 GRAPE
    113 GRAPE
    113 ORANGE
    114 APPLE
    114 APPLE
    114 APPLE
    115 APPLE
    115 APPLE
    116 APPLE
    117 ORANGE
    117 APPLE

  16. Hi,
    I wonder if anyone could help me.
    I want to create a formula which creates an =SUM based on the values of specific cells where a preceding cell contains a specific value.
    For example, I only want the value of D8 counting if D7 contains 1a.
    I'm unsure if there is a COUNTIF formula which would assist with this or if it is an impossibility?
    Thank you in advance.

  17. Please help. I have to figure out how many visits each specific patient had per year. So I have a column labeled service dates ranging from 1/1/2017 to 9/30/2019 and then I have a patient name column.
    I need to know based of each patient how many visits were in 2017, 2018 and 2019

  18. Svetlana!! thank you so much.

  19. I'm trying to count a range of cells in a single column such that I am only counting when "200*" appears in an odd row. My formula consistently returns zero and I'm not sure how/where I've gone wrong:
    =COUNTIFS(E5:E22, MOD(ROW(),2) = 0,E5:E22, "200*")

  20. Is there a way to count matching text between two cells?
    Example:
    A1= red, green, blue, purple
    A2= red, blue
    Matching count= 2

    1. Nevermind, I figured it out.

  21. Hi,
    Im facing a problem. I have automated a sheet and there I have used countifs. However, eg. I have given a cell range i.e I:I and further condition. But when i checked the data for the next month the column changed from I to B and it keeps changing constantly. Can i Have a "&" condition before count if function. E.g if 1:1 contains text "status" then countif the column.

    Thanks in advance,
    Soheb.

  22. appreciated it really help me and done my job

  23. Thank you for this formulas. Do you know if it is possible to combine 2 arrays of OR conditions, with a AND between ? It seems impossible
    For example something like
    =SUM(COUNTIFS($A$2:$A$11,{"apples","Bananas"},$C$2:$C$11,{"cancelled","pending","in transit"}))
    doesn't work

  24. I have an excel summary table that is referencing other table to return a yes or no response. The summary table is 10 contiguous cells in a row that I want to get an average of yes' (out of ten). I tried doing countifs, but now I am not sure if this would be the best function to use.
    ex: C8:L8 have a non numerical result referenced to return "yes" or "no". in M8 I want to display the average number of yes' not just the count.
    any help is greatly appreciated. thank you

  25. I need help with a spreadsheet on google sheets
    I am trying to write a formula. I have a large spreadsheet with a range of dates and tasks that employees have completed that day. All of these dates are at random and the employees enter their name once something has been completed by them.
    I need to write a formula that will search for example the name *aaron* on date 16/09/2019 and count the amount of times *aaron*has been entered and show for example 7.
    Can anyone help ?

  26. I have this query to be sloved i have two or more lines in single row in excel how can i split the same in two different rows below are in single row now i want to split them

    eg
    1 Unit Fodder Grass Bijankur-BB2 50 gm Rs. 335 1 Unit Humic Acid Roota 500 gm Rs. 430
    11 Unit Drumstick PKM 1 50 gm Rs. 2401 Unit Fodder Grass Bijankur-BB2 50 gm Rs. 335

  27. B1/A1-1

  28. how i can count multiple criteria in a single range based on other two conditions using countifs??

  29. Please help – I have a working formula that’s counting three specific criteria from a report (see formula below). I have hundreds of different specific criteria combos that I’ll be counting and would like to avoid having to type all the different criteria combos directly into each formula.

    How can I use cell references instead of typing them? I tried several different things, but unable to get it to work.

    Company1 – Names will be in a fixed cell on the results page – D1
    Department1 – Names will be in a fixed cell on the results page – E1
    Account Type1 – The results page is by account type and all the account types are in the column – A3, A4, A5 (type one is A3)

    =COUNTIFS('Report'!$C:$C,"=Company1",'Report'!$K:$K,"=Department1",Report'!$J:$J,"=Account Type1")

  30. Thank you so much for your tutorials, it's really very much useful in work life.

  31. Date Person Unit Summary
    19 June 2019 Rakesh 32 19-Jun-19 20-Jun-19
    19 June 2019 Rakesh 65 Person U.Count T.Unit U.Count t.Unit
    19 June 2019 Kranti 15 Kamlesh
    19 June 2019 Kranti 25 Kranti
    19 June 2019 Kamlesh 35 Pradeep
    19 June 2019 Kamlesh 48 Rahul
    19 June 2019 Rahul 38 Rakesh
    19 June 2019 Rahul 65 Sagar
    19 June 2019 Sagar 25
    19 June 2019 Sagar 44
    19 June 2019 Pradeep 52
    19 June 2019 Pradeep 35
    20 June 2019 Rakesh 12
    20 June 2019 Rakesh 24
    20 June 2019 Kranti 12
    20 June 2019 Kranti 24
    20 June 2019 Kamlesh 42
    20 June 2019 Kamlesh 35
    20 June 2019 Rahul 48
    20 June 2019 Rahul 38
    20 June 2019 Sagar 65
    20 June 2019 Sagar 25
    20 June 2019 Pradeep 12
    20 June 2019 Pradeep 24
    How this data is create accoding to date person bill count and sum of units

  32. Hi,

    I need some formula to get the below result. Looking formula to get the unique list with conditions & count based on condition.

    Input
    A B C
    Engine Body Status
    Done Painting WIP
    Done Done Wash
    Done Painting WIP
    Done Done
    Done Denting WIP
    Done Done
    Done Body Work

    Done Body Work
    Done Done
    Done Body Work

    Expected Result
    F G
    Yet to GO Job Count
    Painting Wip 2
    Denting WIP 1
    Body Work 3

    Thanks in advance...
    Regard,
    Anand

  33. =IF(I5"",IF(T5"",T5-I5),IF(I5"",-T5,"nopre"),IF(I5"","")
    I neeed help gettin this formular to work. I want to subtract cell values return different value if particular cell is blank and no value if both are blank.

  34. I am trying to calculate students who take only online classes from my data set. I don't want to include students who may take a mix of classes such as online and face to face, or online and hybrid class.Any thoughts how to do this.

  35. I want to count different 17 digit number. Example:
    1. 55554440100000000
    2. 55554440299999999
    3. 55554440178999990
    4. 55554440220000001
    5. 55554440123456789
    Result: 5555444010000000 to 55554440199999999=3 and
    55554440200000000 to 55554440299999999=2
    Please help me to solve this problem.

  36. I'm trying to figure out if two dates in two separate columns are less than today, then they are considered backlogged; however, if a patient was seen on one date for something, then they are not backlogged on the other? How would i figure this out?

  37. How to solve
    F2<150 *3.5 otherwise *6.5
    Note :- * = Multiply
    Reply please send my Email

    Thank you

  38. in col A are student name, col B are Marks, How to around 1000 students are there. I want to find how many students having marks above 500 and how many are below 500. plz help

  39. Hello
    I have a excel sheet for feedback Report and I want to count each faculty feedback with specific alphabet for count separate like:
    Faculty 1 A= 50
    B= 35
    C= 15
    D= 7
    E= 8
    Faculty 2 A= 35
    B= 40
    C= 14
    D= 25
    E= 10
    Same as per above :
    Condition 1: all data calculate in 1 sheet

  40. Hi, this formula counts all apples that falls between two dates. How can I change the formula so that the apples that occurs on weekends between these two dates doesn’t get counted. thanks
    =COUNTIFS(A1:A500,"apples",B1:B500,">="&D1,B1:B500,"<="&D2)

    Column A has names of fruits
    Column B has dates
    D1 is cell with date
    D2 is cell with date

  41. I have a sheet with 4 columns being used which are (A)Job Number, (B)Name, (C)Day, (D)Time
    I have used =COUNTIFS($A$2:$A$149,">1",$B$2:$B$149,"Gary",$C$2:$C$149,"Day 1",$D$2:$D$149,"0600 to 0700")
    I need to count the job number once only as there can be duplicates entered by different names, days or time. How do I use this formula but only count the duplicate job number once.

  42. Hi, any idea how to count 2 criterias (bay4; under 14:00) from the following 2 ranges:
    Bay2 12:12
    Bay3 14:37
    Bay4 13:33
    Bay2 12:58
    Bay4 16:11
    Bay4 15:29
    Bay4 14:41
    Thanks

  43. How to find the count of given numbers whose lenght should be equal to 3 in excel?pls help
    453
    537
    661
    414
    1
    655
    5
    6666
    727
    8
    787
    62

  44. Hi everyone,
    How can I use a multi function formula to extract from the cell what I need.
    For example; in one cell I might have any kind of fruits or vegetables, I want to create a formula to show me this cell if contains fruits then it should read as a fruit or if it contains vegetables than it should show me vegetable.
    Colum A1 I might have fruits such as Apple, Orange, Melon or it could be only vegetable such as Cabbage, Carrots or Broccoli; in B1 I want to show me if it Veg or fruit.
    Can someone help me.
    Regards,
    A.Amiri

  45. 12 13 14 A B D H D E 15 15 17 16 19 20

    HI
    Here some problem to count only alphabet. I means I need to count only alphabet in as per above data so please help.

  46. How do I search a date range with countifs, but exclude the year? For example; " >=01 January" to another day and month...

  47. Need some help, I need to count the number of cells that has a certain word in it based on the date in another cell (the date is formatted 1/4/2019)
    So i have a column with Direct, Strategic, Master (called it Column D)
    I have different dates in the month in Column H
    I wanted to count the number of times Direct shows up for the month of January.

  48. A
    Press Open
    Press Open
    Press Open
    Press Open
    Press Open
    Press Open
    Press Open

    Press Open
    Press Open
    Press Open
    Press Open

    I want to count the number of Press open before the blank space and number of Press open after the blank space. So basically I need a formula that calculates the number of times Press open is repeated every time ore the blank space in the entire column

  49. Sir how to countifs formula use in excel
    Countifs for smaller than 25 in one cell and greater than today another cell Pl give me formula

  50. como usar =SUM(COUNTIFS(Versión_producto,version,country,C5))
    donde> Versión_producto, version y country son Define Name, indicadores de in rango en otra hoja, es que no me esta sumando la data complete.

    su ayuda por favor.

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