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 5. Total comments: 591

  1. Hi

    I'm having difficulty with one of your suggested formulas and in particular one which is covered under heading

    https://www.ablebits.com/office-addins-blog/excel-countifs-multiple-criteria/#count-cells-multiple-criteria-OR-logic

    and sub heading.

    Formula 2. SUM COUNTIFS with an array constant

    The formula is 'SUM(COUNTIFS(range,{"criteria1","criteria2","criteria3",…}))' and it just isn't working for me. I keep getting a warning that it isn't a valid formula.

    Is there a minimum Excel version that this only works with?

    I'm sure I'm applying it correctly but just in case I'm missing something I have a table 49 rows high and column B contains a list of 6 different names which appear randomly and are repeated randomly. Two of the names are Alan & Richard who both appear numerous times so I created the following formula to count the rows that contain either

    =SUM(COUNTIFS(B$5:B49,("RICHARD","ALAN")))

    But, this doesn't work. Please let me know what I'm doing wrong.

    Many thanks.

    1. Hello!
      This function is available since Excel 2007. Please describe your error in more detail. Your Windows may not be using a comma as the list separator, but a semicolon. Also note what kind of quotes you are using.

  2. Which formula do I use if I want to count the number of entries in one column that refers to a specific month (e.g. how many entries for the month of March) that also has an entry (of any written text) in another column (this will be a column that refers to a specific behaviour but could be varied for each row e.g. physical aggression in one row but verbal aggression in another row)?

    Hope you can help.

    2. And to clarify a bit further, I do not want to know how many physical aggressions or verbal aggression entries there are individually but just how many entries appear in that column for a particular month altogether.

  3. Hi there,

    I am having a dataset of people contacting me with Hours of the day (ColA), Date (ColB). Now I want to create a table where I will see how many times in that hour someone contacted me so I can create a hit map. I am quite struggling with what formula to use which will return the number of a contact in that hour of the specific day. Any help?

    1. Hello!
      If you want to get a list of contacts for a specific day and hour, then use the FILTER function. Read this detailed guide.
      If you want to count how many times a person has contacted you at a certain time, then use the COUNTIFS function. Read this article above.

  4. If I have a sheet that contains dates in Column B (Heading row for Column B has a start date in B1 and End date in B2) and multiple other columns, one (Column L) contains specific words.
    How can a count the number of specific words, in Columns L that fall between 2 dates in Column B?

    For example I have dates from January 1 to March 31 in Column B. I have the word Weston in Column L multiple times. I want to find out how many times Weston between between January 10 to January 16.

    I've tried Countif, Countifs, if(and, if(or, nested if. I am pretty good with excel and teach it, but this one is stumping me.

    Sylvia

    1. Hello!
      Please use the following formula/the formula below to solve your task:

      =COUNTIFS(B1:B10,">="&DATE(2021,1,10),B1:B10,"<="&DATE(2021,1,16),L1:L10,"Weston")

      Read this tutorial on how the COUNTIF function works with dates.

      1. Thank you very much. This had been a great help. I will be definitely be referring back to you if I have any other questions.

  5. Hello,

    I'm pulling my hair out. I'm trying to count the number of cells where one range contains specific text and another range is less than today's date. I need both conditions to be true before the cell can be counted. The formula below is not working. Should I use a function other than COUNTIFS?

    =COUNTIFS(C6:NH6,"*-B*",C3:NI3,"<"&TODAY())

    1. Hello!
      The ranges of criteria in the COUNTIFS function must be the same size. For instance:

      =COUNTIFS(C6:T6,"*-B*",C3:T3,"<"&TODAY())

  6. ok, so im trying to figure out how to add 2nd variable onto my formula to count them both. but i cant get anything to work.

    =COUNTIFS(DUE!A:A,"DLA",DUE!C:C,"250",DUE!N:N,"V")

    now this currently works, but i also want to add Y that is also in the same column as "V"

    any help would be greatly appreciated

  7. I need a formula to count if columns A, B, C, OR D have "*apples*" AND column E is not blank.

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =SUM((ISNUMBER(SEARCH("apples",A1:A10,1))+ISNUMBER(SEARCH("apples",B1:B10,1))+ISNUMBER(SEARCH("apples",C1:C10,1))+ISNUMBER(SEARCH("apples",D1:D10,1)))*NOT(ISBLANK(E1:E10)))

      I hope my advice will help you solve your task.

  8. Hi, I doing a sale excel were I need to count all the same product multiply by the amount of order.

    Order product
    2. Apple
    3. Orange
    1 apple

    Were apple should be 3

  9. Hi,

    Thanks for this article. My problem now is it doesn’t count when one cell has the same text, especially two names of the same person.

    The text goes like this:

    G2:G10 = James
    G11:G20 = James Pattinson

    Formula:

    =(COUNTIFS(F:F, $K$5, G:G,{"James", "James Pattinson"},D:D,"Done"))

    The formula refuses to count cells that have the same first keyword. Please advise on how to solve this.

    Paul

    1. Hello!
      I believe the following formula will help you solve your task:

      =SUM(COUNTIFS(F:F, $K$5, G:G,{“James”, “James Pattinson”},D:D,”Done”))

      or

      =COUNTIFS(F:F, $K$5, G:G,“James”&"*",D:D,”Done”)

      I hope my advice will help you solve your task.

  10. Hello,

    In the example "How to use COUNTIFS with wildcard characters"

    How would I count the number of "Mike" in column B when the date in C equals the date in D?

    Kind regards,
    Mitchell

    1. Hello!
      Please try the following formula:

      =SUMPRODUCT((B2:B10="Mike")*(--(C2:C10=D2:D10)))

      Hope this is what you need.

      1. Hello Alexander,

        Thanks for your feedback. Unfortunately I don't get the desired result, but a "value not available error".

        So what I'm trying to do:
        On sheet 1 I have information on deliveries. (suppliers (column N), promise dates(column G), actual delivery dates(column H), and many more)

        On sheet 2 I have listed all suppliers(column A) and added a column(B) with a formula which counts the number of times a supplier made a delivery =COUNTIF(Sheet1!$A$2:$P$268;A2)
        Now I want to add a column(C) which only counts the times this supplier actually delivered on the promise date.(Sheet 1, G=H)

        With your help I tried below formula, which returns a "value not available error".

        =SUMPRODUCT((Sheet1!$A$2:$N$268="A2")*(--((Sheet1!$G$2:$G$268:Sheet1!$H$2:$H$268))))

        Based on above information, is there a formula which can calculate this without adding columns to sheet 1?

        Kind regards,
        Mitchell

        1. Hello!
          I wrote this formula based on the description you provided in your original comment. Please note that if you’d provided me with the precise and detailed description of your task from the very beginning, I’d have created a different formula that would have worked for your case.

          =SUMPRODUCT((Sheet1!$N$2:$N$268=A2)*(--(Sheet1!$G$2:$G$268=Sheet1!$H$2:$H$268)))

          I hope this will help

  11. =COUNTIFS('C.O MONITORING'!X8:X992,"MOIA",'C.O MONITORING'!AE8:AE992,"BELLA")
    this is my old formula. counting the number of contracts executed by a certain person "Bella"

    for new formula
    I want to count the number of the contract executed by "BELLA" per months (with a specific date)

  12. Hi,

    I have data that shows me how many times different projects pass or fail, but I want to count how may times a project fails on different days, i.e. not to count it twice if it failed twice on the same day. See simplified data below; I want to return Project 101 as 'Failing' 3 times, i.e. on 3 days, not 4 individual occasions.

    A B C
    Date Project Result
    01 Jan 101 Fail
    01 Jan 101 Fail
    02 Jan 102 Fail
    02 Jan 101 Fail
    03 Jan 101 Pass
    03 Jan 101 Fail
    04 Jan 103 Pass

    Thanks in advance
    Paul

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IFERROR(ROWS(UNIQUE(FILTER($A$1:$A$8, $B$1:$B$8=$D$1))), 0)

      where $D$1=101.
      You can learn more about count unique values with criteria in this article.
      I hope it’ll be helpful.

  13. I'd appreciate any help or insight.

    Use Case: Get count of multiple criteria from different columns

    Criteria1: =COUNTIFS('Sales Report'!$G:$G,"Damy Dams",'Sales Report'!$H:$H,"Nov",'Sales Report'!$I:$I,{"Movies","Music"}),

    Criteria 2: 'Sales Report'!$E:$E, {"Offline","Onsite","Podcast"})

    My formula returned the count for Criteria 1 BUT does not return the count for Criteria 2

    How can I combine all into one statement?
    For example: =SUM(Criteria1,Criteria2)

    AND NOT

    =SUM(Criteria1+ SUM(COUNTIFS(Criteria2)…. this gives a much higher count value and is not the requirement.

    1. Hello!
      Use the formula for Criteria2

      =SUM(COUNTIFS('Sales Report'!$E:$E, {“Offline”;”Onsite”;”Podcast”}))

      I hope it’ll be helpful.

  14. Assuming I have a table with two columns J and K with entries of rows either, row1 ,(J=No,K= Yes), row2(J=yes,K=No), row 3 (J=Yes,K=Yes) ROW 4, (J=No,K=No), row5 ,(J=No,K= Yes), row6 (J=yes,K=No), row 7 (J=Yes,K=Yes) ROW 8, (J=No,K=No),...onwards for different documents.
    Whenever a No appears on either column J or K or both columns it should be termed error. What formula can I use to help me analyze a large data with the above scenarios. Analysis can be done on another sheet.

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =IF((COUNTIF(J:J,"No") + COUNTIF(K:K,"No"))>0,"Error","Ok")

      I hope my advice will help you solve your task.

  15. I'm trying to do countif to get results from different cells not ranges.

    A1 table
    A2 chair
    A3 table
    A4 leg

    I basically need countif (A1,A3, "table") but that doesn't work.
    I've tried this indirect formula but it still doesn't work =SUM(COUNTIF(INDIRECT({"a1:a1","a3:a3"}),"table"))

    Any ideas?

    1. Hello!
      The COUNTIF function can only work with one range. Therefore your formula is not possible. Try

      =SUM(--(A1="table"),--(A3="table"))

      I hope it’ll be helpful.

  16. I have to calculate attendance for 5 periods in different sheets. Using countif how can i calculate attendance

  17. In a range, is there any formula where i can pick

    how many 1s, how many 2s, how many 3s etc... in one step

  18. Hello
    I have a table of data, on column of which is either ***, ** or * depending on the prioritisation of that row. If I now want to do a countifs function, how would I insert a condition that a row has *** for that specific criteria? I tried using a tilde, " " but cant get it right
    Thanks!

    Adam

    1. Hello Adam!
      To count the number of "***" values, add a tilde before each *

      =COUNTIFS(K1:K40,"~*~*~*")

      I hope it’ll be helpful.

  19. Hi,

    I have some problem getting the formula for my excel file. it took me to much time to solve it even now i cant figure it out. i dont have really much knowledge on excel though. would you mind helping me to create a formula to condense all equal date with another criteria.Example columnA(Dates) columnB(text).To make it more clearer. All May 5,2020 in column A and technical in column B will be count as one.

  20. I need a formula for counting the number of columns in a row with values greater than 5.
    Ex. Out of Column B , D , G - B1 & G1 has the value greater than 5, so I need to get count as 2.. How is it possible.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =SUM(IF(COUNTIF(B:B," > 5")>0,1,0),IF(COUNTIF(D:D," > 5")>0,1,0),IF(COUNTIF(G:G," > 5")>0,1,0))

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  21. I have Count like COUNTIF((C4,F4,I4,L4),">0"), result are not find.
    please help.

    1. Hello Ashok,
      Please try the following formula:
      =COUNTIF(C4,">0")+COUNTIF(F4,">0")+COUNTIF(I4,">0")+COUNTIF(L4,">0")
      Range can only be contiguous cells.

  22. I have a spreadsheet that has three columns I am working with...Column C that has Dates Sampled, Column H that has Quantity, and Column K that has Aggregate type. I need to have a formula that will give the Quantity of X aggregates for each quarter by using the dates sampled to determine the quarters. Is this something I will be able to do with the CountIf function? TIA

    1. Hello Katelyn!
      If I understand your task correctly, please try the following formula:

      =COUNTIFS(C2:C32,">="&DATE(2020,1,1),C2:C32,"<"&DATE(2020,4,1),K2:K32,"AAA")

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  23. Here is my formula. I am trying to count if column K, D, and E meets these criteria. Column K must have Winston. Column D must have Win7 to Win10. Column E has several criteria.
    =SUM(COUNTIFS(K:K,"WINSTON",D:D,"Win7 to Win10",E:E,{"Deferred","Discovered",Discovered by Local IS","Failed 10 Push","Local ITS Support","Not Delivered","Not Found","Special Config on Order"}))

    1. Hello Tara!
      If I understand your task correctly, maybe the following formula should work for you:

      =SUMPRODUCT(--(D1:D10="Win7 to Win10"), --(K1:K10="WINSTON"), IFERROR(MATCH(E1:E10,{"Deferred";"Discovered";"Discovered by Local IS"},0),0))

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  24. Hi,

    I am attempting to track the frequency that sales person makes calls in a selected date range. The formula is adding up the number of times it says Matt Elkin as well as the number of time any of those dates were mentioned. I want it to count those dates only if it says Matt Elkin.

    G2/F2 - start and end dates

    =COUNTIFS(Database!M2:M9359, "Matt Elkin") + COUNTIFS(Database!G2:G10000,">="&G2,Database!G2:G10000,"<="&F2)

    1. Thanks Jason, got solve my problem checking your comment! :)

    2. Hello Jason!
      If I understand your task correctly, maybe the following formula should work for you:

      =SUMPRODUCT(--(Database!G2:G10000>=$G$2), --(Database!G2:G10000>=$F$2), --(Database!M2:M9359="Matt Elkin"))

      Hope this is what you need.

  25. I have a spreadsheet where cells contain answers to a multi-response question. Cells can contain any combination of responses from A - J. I need a way to count the number of cells that contain (for example) A, C, or E. If a cell contains more than one of these, I don't want it to be counted twice. For example:

    Cell 1: A, B, D
    Cell 2: A, C, E
    Cell 3: B, D, E
    Cell 4: B, D, G, J

    The total count of cells that contain A, C, OR E is 3. Using the OR formula above, we would get a total of 5, as the count for A is 2, the count for C is 1, and the count for E is 2. Is there any way to do this?

    1. Hello MLC!
      If I understand your task correctly, the following formula should work for you:

      =SUM(COUNTIFS(A1:A16,{"*A*";"*C*";"*E*"}))-SUM(COUNTIFS(A1:A16,{"*A*C*";"*C*E*";"*A*E*"}))

      This is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter.

  26. Seeking a formula:

    Currently use:
    =countifs((A1:A25),">="&DATE(2017,1,1),(A1:A25),"<="&DATE(2017,1,31))

    We now want to select

    Q1:Q25 if contains the letter "C" and also fits within the above date ranges...

    Please help

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =COUNTIFS((A1:A25),">="&DATE(2017,1,1),(A1:A25),"<="&DATE(2017,1,31),(Q1:Q25),"=C")

      Hope it will help you.

  27. How would you type a formula to find out if the sum of two cells is greater than or equal to 3? I tried entering COUNTIFS(A1+M1,"=<3") but was unsuccessful. I need to count multiple pairs of cells, not just one pair at a time.

    Thanks

    1. Hello,

      Please try the following formula:

      =SUMPRODUCT(((A1+M1)<=3)*1)

      Hope it will help you.

  28. I am trying to add the number of Job Titles (Column C) and their Status - Open, Filled (Column B).

    I have tried Count if and subtracting the data but it doesn't seem to be working.

    =COUNTIF(C11:C67,"CSR",+ B11:B60,"O")
    How can I get the number of CSRs that are open between Column C and B?

    1. Hello, Jaime Manale,

      Please try the following formula:

      =COUNTIFS(C11:C67,"CSR",B11:B67,"O")

      Hope it will help you.

  29. Heya! I am having a hard time getting a formula to work that counts a Y in one of 3 columns to the right if a rep's initials are in the column to the left. So I've tried:

    =SUM(COUNTIFS(A3:A1002,{"JM"})+COUNTIFS(G3:G1002,{"Y"},I3:I1002,{"Y"},K3:K1002,{"Y"}))

    =COUNTIFS(A3:A1002,"JH", G3:G1002,"Y", I3:I1002,"Y", K3:K1002,"Y")

    I can get it to work with one, but not all 3 columns (which are for 3 sessions in one day).

    1. Hello, Cher,

      try this one:
      =SUM(IF((--($A$3:$A$1002="JH"))*((G3:G1002="Y")+(I3:I1002="Y")+(K3:K1002="Y"))>0,1,0))

      Note, that this should be an array formula and you need to press Ctrl + Shift + Enter instead of Enter.

      The formula adds 1 whenever there's 'JH' in column A AND any of G,I,K columns contain 'Y'.
      Hope it helps.

  30. It is a misnomer in this article to call the formula a COUNTIF function with OR - as you are adding duplicate values (this should be called AND).

    I am working on a dataset and would like to identify patients who had any assisted reproductive technology to become pregnant.
    Patients selected yes or no to whether they had surgery, IVF, or prescription medications (in 3 columns). I do not want to count someone who had IVF and prescription meds, for example, as two values. The only way I know how to do this is with a helper column - is there another way?

    Thanks!

    1. Hello Jenny,

      Excel's COUNTIFS function counts only those cells that meet all of the specified criteria. We call it AND logic, because it is how Excel's AND function works.

      If you want to count cells what meet at least one of the specified criteria (any of the specified criteria), add 2 or more COUNTIF functions. We call it OR logic, because Excel's OR function works this way.

      As for your task, please be a bit more specific - describe what columns contain what values and what exactly cells you want to count.

  31. hello, I'm struggling with the countifs function.

    I'm puttting this in my excel and it gives me an error message all the time!
    Do you know what it can be???
    =COUNTIFS("E1678:E2515,"=review",M1678:M2515,"<30")

    It's killing me!

    1. Hi Hugo,

      There is an extra double quote character before E1678:E2515. Once it's removed, your formula works perfectly:
      =COUNTIFS(E1678:E2515,"review",M1678:M2515,"<30")

  32. Hello Svetlana, I've been trying unsuccessfully to find a formula to count numbers that start with certain digits.For example, in a range that includes: 4040345, 40503543, 4065678, 4067865, 4079876, I want Excel to count only those numbers that start with "406" (2 in this case). I tried a combination of Countif + Left but it did not work. Do yo have any idea of a formula that does this calculation? Thank you!!!!!

    1. Hi Diana,

      You can use an array formula similar to this:
      =SUM(--(NUMBERVALUE(LEFT(A1:A100,3))=406))

      Where A1:A100 is the data range with your numbers.

      Please remember to press Ctrl+Shift+Enter to complete the array formula correctly.

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

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

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

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

    Regards,

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

  38. Hello,

    I am trying to create an excel formula based on a tree risk assessment model with three variable factors:

    1) The size of part likely to fail
    2) The frequency of use of the area, and
    3) The probability of failure (PoF).

    Each variable has a range between 1 and 6. For example if the size of part equals 3, the frequency equals 2 and the PoF equals 2 then the overall risk is given as 1/4000.

    I have the table with all of the possible combinations of the 3 variables listed, each correlating to an overall risk ranking. My question is: Is it possible to create a formula based on the table to retrieve the specific risk ranking based on a random combination of the three variables?

    Any light shed on the topic would be greatly appreciated.

    Thanks,
    Andrew

  39. how would I countif the criteria is a date and I want to count if the year matches?

    1. Hi Stacy,

      You can use the YEAR function to extract a year from a date.

      It's difficult to suggest an exact formula without knowing anything about your data.

  40. Hi Svetlana. Is it possible to use Countifs to pick up one of the criteria from a specific cell? I have tried, but it only ever returns a 0 value.

    For example, I have a list of companies in column A, the month in which an order was delivered in column B, and a note of whether the goods were OK or faulty in column C. I want to find out how many orders were delivered faulty in a given month.

    The formula
    =COUNTIFS(B3:B100,"=03 (14/15)",C3:C100,"=Faulty")
    works fine, but would it be possible to use another cell (let's say B102) to enter the month I am looking for and get Countifs to pick up and use the criteria from that cell?

    Many thanks.

    1. Hi David,

      If my understanding it correct, you want a formula to "extract" a month number (03) from values in column B. If all the values in B have the same pattern like 03 (14/15), including a space before the opening parenthesis, you can use the following wildcard in criteria1:

      =COUNTIFS(B3:B100, "03 (*", C3:C100,"=Faulty")

      As for entering the month is a separate cell, probably it's also possible, but I cannot think of such a formula at the moment, sorry.

      1. David,

        Here's how you can count cells based on the month number in cell B102:
        =COUNTIFS(B3:B100, B102&" (*",C3:C100,"Faulty")

        Important! You should enter the month number in cell B102 exactly as it appears in column B (03 in your example). And since Excel cuts off leading zeros in numbers, 03 shall be entered in cell B102 as text (Home tab > Number group > Text).

  41. In column Q, I need a formula that will count all of the cells that have ROH,EXEC,and STAFF. I tried using the format below but it did not work.

    =COUNTIF(Q11:Q275,"ROH") + COUNTIF(Q2:S11,"EXEC") + COUNTIF(Q11:Q275,"STAFF")

    1. Hello Melony,

      In your formula, the second COUNTIF contains a different range, and this may be the cause of the problem.
      Try the following formula:
      =COUNTIF(Q11:Q275,"ROH") + COUNTIF(Q11:Q275,"EXEC") + COUNTIF(Q11:Q275,"STAFF")

  42. Hi Svetlana -

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

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

    Thanks,

    Javier

    1. Hi Javier,

      If you have a list of those 70 words in some column, you can reference the first cell in the formula, say $A13, and then copy the formula down to other cells:
      =COUNTIF($A$1:$LP$12, $A13)

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

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

    1. Hi Andrea,

      You can use the following array formula (Remember to press Ctrl+Shift+Enter to enter it correctly):

      =SUM((LEN(A1:A100) - LEN(SUBSTITUTE(A1:A100, 0, ""))))

      Where 0 is the digit you want to count.

      Instead of supplying a numeric value in the formula, you can place it in some other cell, then the formula will be slightly different, as in the following example: Array formula to count any given character in a range.

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

    1. Hi Kim,

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

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

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

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

  45. Hello Svetlana,

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

    =COUNTIFS('Sheet1'!D1:D10,'Sheet2'!C5,'Sheet1'!I1:I10,'Sheet2'!">="&'Sheet2'!G3)

    What would be the right expression? Thanks a lot,

    Jose

    1. Hi Jose,

      The criteria can be expressed as follows:
      =COUNTIFS(Sheet1!D1:D10,Sheet2!C5,Sheet1!I1:I10,">="&Sheet2!G3)

      Is this what you are looking for?

  46. Hi,
    I have a countif function that is working:

    =COUNTIF('Sheet1'!C:C,"<=10/07/2015")

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

    Please help!

    1. Hi Rachel,

      The following formula seems to be the one you are looking for:
      =COUNTIF('Sheet1'!C:C,"<="&$AI$1)

  47. Dear Svetlana,

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

    Kindly suggest

    1. Shubham,

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

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

  48. Hi Svetlana,

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

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

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

    =COUNTIFS(C5:C9,"P",C5:C9,"h",C5:C9,"s",C5:C9,"m")

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

    Can you help?!

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

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

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

      Thanks for providing the inspiration to sort it!

      1. Thank you

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

    1. Hi JOLLY,

      You can add up the results of 2 COUNTIF functions, like this:

      =COUNTIF(A1:F500, "yes") + COUNTIF(A1:F500, "na")

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

    Thanks in advvance for ur assistance

    1. Hi!

      Supposing that Column a is "pens" and column B is color, you can use the following COUNTIFS function:

      =COUNTIFS(A1:A100, "pens", B1:B100, "red")

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)