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

  1. 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?

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

  3. 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?

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

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

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

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

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

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

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

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

  12. Svetlana!! thank you so much.

  13. 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*")

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

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

  16. appreciated it really help me and done my job

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

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

  19. 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 ?

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

  21. B1/A1-1

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

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

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

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

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

  27. =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.

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

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

  30. 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?

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

    Thank you

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

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

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

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

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

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

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

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

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

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

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

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

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

  45. Hello All,
    I came across a situation like , i have imported a querry from TFS. B & C are my column
    There are some values in column C which is the subset of Column B.
    Exmaple Column B say Items , C will have values of Vegetable , Fruits and some more.
    Column B again have Drinks, C will have values of Wine, Fruitdrink, Soda...
    Here i need to check count many C items are depends on B. I am not sure how to tackle this in Excel.
    BR/ Deepak S

  46. Hello All,
    I came across a situation like , i have imported a querry from TFS. B & C are my column name where c value is depends on a value. Here i need to check how many C items are depends on B. I am not sure how to tackle this in Excel
    B C
    x -
    y
    y_1
    A -
    a
    b
    BR/ Deepak S

  47. Hi Everyone-Hoping someone can help me figure out a formula to do the following task.
    I have staff names in column B, Client name in column C, Hours worked in column G, Category or account they charged their time to in column H. I am trying to figure out how many hours each person works on a client but they may charge different accounts so there are multiple entries per client by a staff person. then the goal is to know how many times a person works say 30 hours or more on clients. Any help would be appreciated, thanks.

  48. Dear Concern,
    I would like to know how to use countif formula in Profession tax
    If Male count no of 200 & 175
    if female count no of 200 & 175

  49. How to count series in a row?
    1,2,3,5,6,8,10,12,20,21
    ans: 3
    (1,2,3),(5,6),(20,21) there are three series.

  50. How can I simplify the following?
    =COUNTIFS(C79:AH79,">=1",C79:AH79,"=1",'CC-1210'!C79:AH79,"=1",'CC-1220'!C79:AH79,"=1",'CC-1240'!C79:AH79,"=1",'CC-1243'!C79:AH79,"=1",'CC-1247'!C79:AH79,"=1",'CC-1250'!C79:AH79,"=1",'CC-1255'!C79:AH79,"<=14550")
    I have 8 spreadsheets and I would like the number of times a value falls into the range above. I have to repeat this for two other ranges. The above method works but is very long.

    Thanks
    Carlo

    1. Three subjects marks is their in excel sheet but 40% below marks is coming subject name come to result
      How to get the result.

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