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 25. Total comments: 2057

  1. Hi,

    I want the formula to count with 2 conditions - first it should filter column for a particular text and then it must filter another column in a particular date range.

    I am using the below formula:
    =COUNTIFS((Sheet 1!B:B,"ICT_W*"), COUNTIFS(Sheet 1!C:C,">=12/31/2017")-COUNTIF(Sheet 1!C:C, ">1/1/2017"))

    This is not working, please guide.

    Thanks & Regards,
    Derok

  2. Come up with dummy data for the following: Quiz-I and Quiz-II, Assign-I and Assign-II as
    well as capturing data for CAT. The total for all these components should add up to 40%
    (Course Work), Exam weight is 60% (but given out of 90) and Grand total is 100%. Use
    appropriate functions for analyses of these results. Grade the students as follows:
    Marks Grade Accreditation:
    72% and Above A DISTINCTION
    65 – 71 B UPPER CREDIT
    55 – 64 C LOWER CREDIT
    45 – 54 D PASS
    44 and Below F FAIL
    All the Quizzes were given out of 45, Assigns were given out of 60 and the CAT was given
    out of 55. The relative weight for the two Quizzes is 6% each; 8% each Assign and 12% for
    the CAT. Grade the Students using the IF function. Use the COUNTIF or LOOKUP function
    to compute grade summary for the test. Format the sheet (The data should have at least forty
    (30) entries). Print a copy with formulas displayed and another copy with actual data.

  3. Hi all,

    Could any please help on the below?
    I want to count the number of cells using 2 different criteria
    1. Countif date is greater than date, eg =countif(A1:A10,">"&B1)
    2. Countif cell is blank, otherwise give ''text''
    I know I need to use the formula ''Countifs'', however when I try to put them together it shows the message Value, clearly I'm doing something wrong!

  4. I have created four sheets in Excel to record monthly attendance in church. The first sheet records the Guest that are attending for the first time(first time guest list), the second sheet Records guests that attended for the second time (i.e same guest attending again). The third sheet is for analysis where I plan to display the number of total First time guests showing how many are male and female). My problem is I have done the total guest per month it works well, but i am not able to extract the number of Male and female from the (first time guest list). Sheet1(Month, Name, Gender,FirstTime) Sheet2(Month, Name, Gender,SecondTime), Sheet3(Month, Total, MaleTotal, FemaaleTotal)

  5. Hi
    I need to know which formula is put in condtion :-
    if someone do 12 hrs duty in a day but we take 8hr per day & 4 hrs as an ot(over time) payment should be make by diff rates of 8hr & 4rs ot.

  6. I have a requirement which involves countif. I have a sequence of Tif files
    0557_0001 - sequence 2
    0557_0003 - Sequence 4
    0557_0007 - Sequence 3
    The file no 0557_0001 has 2 pages which is refereed as sequence ie is Sequence 2.
    When we add : file no 0557_0001 + 2 pages gives 0557_0003
    I have used =COUNTIFS(B:B,B1) to get the sequence.I need to include the addition part and populate which does not tally the sequence.
    Example: When we add : file no 0557_0001 + 2 pages gives 0557_0003 but if i have the next file as 0557_0004 , it should populate the difference as -1.

  7. I need a formula that finds the words "Email Sales" in column A:A and adds the amounts that correspond from column B:B. In the below example, my result should be $32,000

    Email Sales $15,000
    Email Sales $2,200
    Ads $3,000
    Ads $5,000
    Email Sales $2,800
    Ads $8,000
    Email Sales $12,000

  8. In your Product, Amount, Status data range, how would you find the Number of Fruits Canceled?

    So in this example, the goal would be to get a unique count of Product that was Canceled. Bananas and Apples show up twice, but the result would only return the unique instances of Canceled for each. Thank you!

  9. I have a column for genders and one for diferent races
    I need to use excel to calculate number of males and also females for each race separating the males and females

  10. Hello,
    I was wondering if you could help me please - I need to create a formula that counts the number of times there is an overdue date in column I, where the corresponding cell in column J is blank.
    A date is entered into column H (date appointed), there is a formula to auto-fill column I (date due) which is =IF(ISBLANK(H6), " ", EDATE(H6,6))
    There is also conditional formatting on column I which turns the date red if it meets this criteria. =AND($I6<TODAY(), $J6="")
    An entry into column J (date reviewed) results in the conditional formatting being removed, so that it returns to black.
    I need to create a formula to count the number of dates in column I which are older than today but only if the corresponding cell in column J is blank.
    I have tried =COUNTIFS(I6:I260,"<TODAY()",J6:J260,""&"""") but suspect it is counting the auto-fill formula in column I. Please help!!!!
    thanks
    -C

  11. I are in need of some help. I know a little bit of excel but not what my boss needs. Column C has the dates that items will be shipped, column d has if the item will be lined, column e has if the item will be blasted, and column f has if the item will be painted. She is wanting me to have a formula calculate the items for each column on a certain date, if that makes sense. For example: On day 04/12/2019, we want to know how many items will be shipped that were lined, blasted or painted separately. for each cell, there is the dollar amount.

  12. Is there any way to count non zero cells when cells to be checked are not in a series. For example, I want to count non zero cells from these 5 cells A1, B23, C21, A11,D13

  13. AMC End Date
    31/01/2020
    How to put conditional format for above date before 30 day's it should reflect in red color means it's come for due.
    Kindy help with countif conditional formulas

  14. IF SUPPOSE CELL HAVING 31-JAN-2020 IS DUE DATE, BUT BEFORE 300 THE SAME CELL NEEDS REFLECT IN RED COLOR

  15. When I use Countif, I highlight the range which enters correctly (C2:C295, but when I click the first cell that I want to count for the criteria it just enters "Extract" in the field and doesn't work. I've done this before and always have issues but eventually figure it out. Why doesn't it just read the value when I click the cell? Why does it say Extract and then not count how many times the value appears in the range????

    1. I copied the criteria column and pasted as values into another column since apparently excel can't see the values as they are so now not getting extract when I select the criteria. However, when I hit enter - the cell just displays the formula rather than the results!!! So my cell says =COUNTIF(C2:C295,E2) instead of "4" or whatever the count would be. wth am I doing wrong??????????????O

  16. I cant figure out my error =COUNTIFS(G2:G75,"PAC", Q2:QR75,"Pending", COUNT L2:L75,"Case Manager")

  17. Hi, Can you help me with this?

    I want to get the numbers of "banned" items on a specific date? if my list is included with deleted items.

  18. Hi there,

    Am I able to use the ABS function on this formula somehow?

    =SUM(COUNTIFS('Sheet1'!A$2:A$191,{"0*","1*","2*","3*","4*","5*","6*","7*","8*","9*"},'Sheet1'!E$2:E$191,">"&F8))

    I tried the following but it is not working:

    =SUM(COUNTIFS('Sheet1'!A$2:A$191,{"0*","1*","2*","3*","4*","5*","6*","7*","8*","9*"},ABS('Sheet1'!E$2:E$191),">"&F8))

    Is there something I am missing here? Tried all sorts of strategies around it and I am not able to do it. Please help.

  19. how do we split during COUNT when A1=bc
    we want b1 shows 1 for A1 =bc & c1 shows 1 for A1=bc

  20. I have a question with Countifs..

    Can i have the criteria rage more than 1 column ?

    eg: I have 3 columns which has "Critical, High & Medium" and one column with countries (India , Pakistan).

    Now i need to know how many critical in India.

  21. Hi ,

    Kindly please help ,
    I wanted to countif there is "3" and there is differences in Row 1 , than it will count , I should have the answer of 2
    ( Under Apple and pear there is "3" )
    How should I compute the formula .
    P/S : I want to count Row B "3" if there is Unique data in Row A.

    Apple Orange Pear Apple Apple Orange
    3 3 3

  22. Hi,

    Kindly help me, I have some criteria to be defined from below table:
    Master Try 1 Try 2
    G G R
    G G R
    G G G
    R R R
    R G G

    From above you see G (good) and R (reject). What I what to count is correctly decision and falsely decision referring to master. The table will like below:
    CORRECTLY FALSELY
    G R G R

    Please advise for the formula,

    Thanks in advance
    Aufa

  23. I am trying to create a formula where I am counting only answers that are NA or Pass. I am able to successfully create the formula if i am just counting Pass, but not both. I am not using a range. The formula that works for just pass is: =SUM((COUNTIF(INDIRECT({"B10","B13","B17","B18"}),"Pass")*D10),"0")

    How do I also make it add NA?

  24. looking for some advice, I'm trying to count different values in excel or sheets.

    example: counting sick days for employees.

    Full sick day being 1
    half sick day being 0.5

    SICK = 1
    SICK/ = 0.5 (this means the first half of the day was sick)
    /SICK = 0.5 (this means the second half of the day was sick)

    How can I build something that recognizes SICK SICK/ and /SICK and also adds up the different values of each. Thanks in advance.

  25. good evening
    kindly help me out

    i have a excel table included with departments and dates

    i have 4 types of departments and dates from jan to dec
    i need to combine the BELOW GIVEN BOTH forumla IN ONE like example given below

    =COUNTIFS(A2:A7,"Logistics Department")
    other for formula is

    =COUNTIFS(B2:B7, "="01/01/2018)

    MY MAIL ID maqbul2005@gmail.com

    1. Hello Maqbul,
      Please try to apply the formula below:

      =COUNTIFS(A2:A7, "Logistics Department", B2:B7, "="&DATE(2018, 1, 1))

  26. The project's owner wants to know the percentage of the student pass the exam(per class & subject) in a table where the row is the class and the column is subject. The issue here as the student had repeated the exam different times in both different classes and subjects. The owner is accepting to have one exam result within a three month period if the student repeated the same exam in the same class and for same subject.
    I used countifs but its really exhausting exercise for huge data. Is there any other solution ?!!

  27. I have huge excel where the data duplicated. The frame of the table has:
    1- Student name "Duplicated"
    2- Class "Duplicated"
    3- Subject "Duplicated"
    4- Exam as Pass,Fail.Pending. "Duplicated"
    5- Date "Duplicated"
    6- Record of data entry is IDENTICAL.
    The project's owner wants to know the percentage of the student pass the exam(per class & subject) in a table where the row is the class and the column is subject. The issue here as the student had repeated the exam different times in both different classes and subjects. The owner is accepting to have one exam result within a three month period if the student repeated the same exam in the same class and for same subject.
    I used countifs but its really exhausting exercise for huge data. Is there any other solution ?!!

  28. Dear friends, help to simply my work in excel. I have daily stocks data in A1:A100 for day 1 and B1:B100 for day 2. Now i want to compare B column with A and result to display in C as how many times increased or decreased. i.e 2X times increased, 5X times decreased like.

    Is there anyway to setup formula for my requirements...

  29. In each row (twenty schools) I have two columns (B, C) of attendees (student, non-nonstudent). How would I count whether a school is represented by either a student or a non-student (i.e., count if b=>0 or if c=>0)?

    I tried =countifs (B3:B22,">0", C3:C22,">0") and tried =countifs (B3:B22,">0") + countifs (C3:C22,">0"). Neither of these worked.

    1. Could you please send me the table, so that I can do it.

      1. good, nearer same problem i am facing

  30. Dear,

    Please help me with the table. I would like to change the data when I change the Month. It should change only the data from the table. Month is in dropdown list.

    October ACC AOP ASC
    DS 98.91% 93.33% 93.18%
    KD 100.00% 75.50% 88%
    HP 86.91% 89.13% 88%

    Regards,
    MZA

  31. I am trying to figure out a formula that if P2 is =1000 add 120 days to the date in M2. I cannot seem to get it to return a correct answer. Any help is greatly appreciated.

    1. I should clarify that cell M2 is formatted as a date.

      =Countifs(M2,P2,">=1000",M2,120,P2,"<999",M2,90)
      I'm sure my formula is no where close to where it needs to be.

  32. Partha:
    You can count multiple criteria by using the COUNTIFS function.
    In your case where the first column is in A67:A81 and the attendance is in B67:B81 put "Mad" in C69, "Dad" in C70 and "Chad" in C71. Then in D69 enter =COUNTIFS(A67:A81,C69,B67:B81,"P")and in D70 enter =COUNTIFS(A67:A81,C70,B67:B81,"P")and in D71 enter =COUNTIFS(A67:A81,C71,B67:B81,"P").
    You'll have the attendance for the Nov range next to each name.

  33. how i will calculate the total number of attendance,like Absent and Present in a month? my excel work sheet is given below:-

    (A=Absent,P=Present)

    01-Nov
    SE Name Remarks
    Mad A
    Dad P
    Chan A

    02-Nov
    SE Name Remarks
    Mad P
    Dad A
    Chan P

    03-Nov
    SE Name Remarks
    Mad P
    Dad P
    Chan P

    Pls.help me...

    Thanks.
    P.Biswas.

  34. I am searching on how to count cells based on specific text within a date range.

    E.G. I have a column with codes: UA, RO, etc. I have another column with the date the the code was created.

    I need to count the number of cells with UA from 09-01-2018 and 09-30-2018.

    I find the syntax for counting a cell with the text "code" and how to "sum" numeric values in a date range. I just don't seem to be able to find the syntax that combines both counting cells for specific text within a date range.

    Anybody know how?

    1. Yes I can help u please send that issue to me in E-mail.
      Thanks,

    2. I have a very similar issue. did anyone ever respond?

      1. Did u sort it out your query?

  35. Good Afternoon,
    I have created a checklist in Excel with checkboxes under each heading (3 headings) with the option of TRUE,FALSE or #N/A (as some of the items may not be applicable). I have assigned a cell to each check box. I am trying to capture the completion rate of the items required in my checklist (both when required and/or applicable information has been received). I have used COUNTIF to capture the TRUE data in each subheading, but I cant seem to figure out a formula to capture the required data and additional data when applicable to score as available points and actual points. Any ideas?

  36. how can i found the dates which is marked as leave in one month attendace.

    like we have mentioned all dates from 1 to 30 Aug'18 in one row continuesly
    under that one employee attendance was mentioned like present and leave

    only two days 17 Aug 18 and 27 Aug 18 was on leave.

    how can i found the leave dates using formula in excel

  37. i have to prepare 24 entries (hourly basis) daily sheet and at the end of month i have to add up 00:00 hrs of every day reading in next sheet. is there any formula which i give it 1 ference cell and it can count and add every 24 cell.

  38. i have to prepare 24 entries (hourly basis) daily sheet and at the end of month i have to add up 00:00 hrs of every day reading in next sheet. is there any formula which i give it 1 ference cell and it can count and add every 24 cell.

  39. Dear all,

    I have faced the problems with xl operations
    for example i have 2 sheets one is abstract and one more is detail sheet
    i want to bring counts from details sheet to abstract sheet how it is possible
    Details sheet columns we have like module column(A,B,C,D), Types(CR,PR,QR), priority(high,low,medium) how to shown counts in abstract sheet ->(module Wise, no. of CR, no of PR, no QR)counts
    Please explain the formula

  40. Hi,
    I need a formula.
    4 columns (Name,Purchase,Sale,City)
    I want if purchase cell contain specific word like Nil other than number then count nil with concatenation of city like
    | date 04-10-2018 | |Date 05-10-2018 |
    Name Purchase Sale City | Name Purchase Sale City
    Peter 10 5 Manchester| Peter Nil Denmark
    Rock Nil Denmark | Rock Nil Manchester
    Sam Nil NewYork | Sam Nil Denmark

    | date 06-10-2018 | |Date 07-10-2018 |
    Name Purchase Sale City | Name Purchase Sale City
    Peter 10 5 Manchester| Peter Nil NewYork
    Rock Nil Denmark | Rock 10 5 Manchester
    Sam Nil NewYork | Sam Nil Denmark

    result should be show

    column name
    count of Nil NewYork | count of Nil Denmark | count of Nil Manchester
    Peter 1NewYork 1Denmark
    Rock 2Denmark 1Manchester
    Sam 2NewYork 2Denmark

    Please give me formula of this statement.

  41. Hi all... I'm looking to countif unique values that occur on a certain date with one type of criteria also occur across a separate date range with different criteria at least once, so if my data set (for columns A, B, C) looks like this

    Date ID Activity
    9/1/18 266 Registered
    9/2/18 233 Registered
    9/3/18 266 creative
    9/4/18 235 budget
    9/5/18 267 creative
    9/6/18 266 creative
    9/7/18 233 creative
    9/8/18 287 creative
    9/9/18 298 budget
    9/10/18 288 budget

    I want to find unique instances of the IDs that registered on 9/1 but then show how many of the unique IDs that registered on 9/1 actually did at least one activity (doesn't matter which) in the first, second, third week following 9/1).

    This (example of week one) isn't working for me:
    =COUNTIFS($A:$A,"=9/1/18,$C:$C,"registration",$A:$A,">=9/1/2018",$A:$A,"<=9/08/2018",$C:$C,"registration")

    Can anyone help?

  42. Seeking a bit more advanced application of this logic. There are many approaches to summarizing data; however, COUNTIFS seems to offer a unique function when comparing data against itself. For instance assume the following data set:

    Material Order Type
    a YO03
    b YO01
    f YO03
    d YO03
    a YO01
    f YO03
    d YO01
    f YO03
    a YO01
    a YO03

    If I enter a function as follows:

    =LARGE(COUNTIFS([Material],[Material],[Order Type],"YO03"),1)

    The result is 3, the count of the most frequent material with Order Type YO03. Combining this with INDEX and MATCH it can return the value associated with 3 as follows:

    =INDEX([Material],MATCH(LARGE(COUNTIFS(([Material],[Material],[Order Type],"YO03"),1),COUNTIFS(([Material],[Material],[Order Type],"YO03"),0))

    This formula returns material f, the material with the most orders of type "YO03". My trouble is I want to evolve this formula to rank based on 5 criteria. I would like a formula that calculates a RANK on five Criteria, by doing a COUNTIF on the same above, then adding a similar rank for order quantity, another order type, and respective order quantity, followed by the row as a tie breaker. I've managed to do this with a helper sheet but I hope to do this in one step. My helper formula looks like this:

    =IF([@Material]="","",1
    +COUNTIFS([Emergency Orders],">"&[@[Emergency Orders]])
    +COUNTIFS([Emergency Orders],[@[Emergency Orders]],[Emergency Quantity],">"&[@[Emergency Quantity]])
    +COUNTIFS([Emergency Orders],[@[Emergency Orders]],[Emergency Quantity],[@[Emergency Quantity]],[Stock Orders],">"&[@[Stock Orders]])
    +COUNTIFS([Emergency Orders],[@[Emergency Orders]],[Emergency Quantity],[@[Emergency Quantity]],[Stock Orders],[@[Stock Orders]],[Stock Quantity],">"&[@[Stock Quantity]])
    +COUNTIFS([Emergency Orders],[@[Emergency Orders]],[Emergency Quantity],[@[Emergency Quantity]],[Stock Orders],[@[Stock Orders]],[Stock Quantity],[@[Stock Quantity]],[Row],"<"&[@Row]))

    I'm struggling to introduce SUM(IF(s)) into this statement and maintain the analysis by material. And perhaps this is just too darn complex to convey in writing?

    1. Here is my data:

      ROW Material Order Type Qty
      1 a Emergency 1
      2 b Stock 2
      3 f Emergency 3
      4 d Emergency 2
      5 a Stock 6
      6 f Emergency 3
      7 d Stock 2
      8 f Emergency 6
      9 a Stock 4
      10 a Emergency 3

      I'm working to assign a multilevel rank in one cell to return the material that has the greatest number of emergency orders, than Emergency Qty, Stock Orders, and Stock Qty with ROW as a tie breaker. I've managed to do this with a help column but the analysis takes to long when working through 500k lines. So I'm trying to do this to identify the top 25-50 materials in one step. So far I've assembled the following code but I cannot seem to SUM by ROW the matrix data to return the ranked match. Here is my code so far:

      {=INDEX(Orders[Material],MATCH(0,
      (((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))<(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))))*1+

      IF(((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))=(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency")))),((SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))<(TRANSPOSE(SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))))*1)+

      IF(((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))=(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency")))),IF(((SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))=(TRANSPOSE(SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency")))),((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Stock"))<(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Stock"))))*1))+

      IF(((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))=(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency")))),IF(((SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))=(TRANSPOSE(SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency")))),IF(((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Stock"))=(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Stock")))),((SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Stock"))<(TRANSPOSE(SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Stock"))))*1)))),0))}

      1. Correction to the equation (I don't see where I can upload the spreadsheet in the comment section):

        {=INDEX(Orders[Material],MATCH(ROW()-2,SMALL(SUM(
        (((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))<(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))))*1+

        IF(((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))=(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency")))),((SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))<(TRANSPOSE(SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))))*1)+

        IF(((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))=(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency")))),IF(((SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))=(TRANSPOSE(SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency")))),((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Stock"))<(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Stock"))))*1))+

        IF(((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))=(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency")))),IF(((SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))=(TRANSPOSE(SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency")))),IF(((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Stock"))=(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Stock")))),((SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Stock"))<(TRANSPOSE(SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Stock"))))*1))))),ROW()-1),0))}

        1. I got this ridiculous thing to work...with 4 level ranking. Now to add a fifth level tie breaker for materials that have like order activity. Let me know if you have any suggestions. Changing MATCH(0 to MATCH(ROW() will allow next level matches in the array. Here's the code:

          =INDEX(Orders[Material],MATCH(0,

          IF((--(MMULT(--((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))0))))>0,1,0)+

          IF((--(MMULT(--((SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))0))))>0,(TRANSPOSE(SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))),0)),--(ROW(Orders[Material])>0))))>0,1,0)+

          IF((--(MMULT(--((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Stock"))0))))>0,1,0)+

          IF((--(MMULT(--((SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Stock"))0))))>0,1,0),0))

  43. I'm having an issue here:

    for those names that fall under P:P may not match F4 exactly, because of an initial in the name, how would I write this to count if name is similar when looking up on different tab?

    =IFERROR(COUNTIFS('Issues w-out Action Plans'!K:K,"< 30",'Issues w-out Action Plans'!P:P,'MoEVM Summary Level'!F4),"0")

  44. Can someone help me make this formula work?
    =COUNTIF(B33,E33,H33,K33,N33,Q33,T33,W33,Z33,AC33,AF33,AI33,AL33,AO33,AR33,AU33,AX33,BA33,BD33, ">70%")
    I only want these cells condidered in the count if because they contain student data for a paticular test.

    1. Dawnica:
      The COUNTIF function should be setup like COUNTIF(Range,Criteria). So in your case if the data range is B33:BD33 and the criteria is values greater than 70 the formula looks like this:
      =COUNTIF(B33:BD33,">"&70)

      1. Thanks for the help!
        But what id there are cells in this row that I do not want included in the countif. is it possible to exclude cells?

        That is, in my formula attempt I wanted to get the number of students who scored above 70 on test 1
        When I put in the formula you provided it every cell in the row so it is counting scores above 70 for all tests given so far. Is there a way to do this or would I need a different type of formula entirely?

        1. Dawnica:
          What are the other criteria for the tests you want to count? Are they by date or a test number or something else?
          Which version of Excel are you using?

  45. Dears,
    I have three criteria in column "C", "D" and "E"to consider for customers. i want to have another column "F" wherein i would like to get one value after which i shall use the command. But how can i write an if statement which is basically"
    If the value in column "C" is zero, consider the value in Column "D" and if column "D" is zero, then consider the value in column "E".
    Thank you

  46. Hi. I'm looking for help writing a coutif formula. I have a column with a list of dates. I'm looking to count all dates that are greater than TODAY(). I don't know how to write the condition to check each cell in the range against the TODAY() function. Please assist. Thank you!

    1. Aaron:
      Here's the formula to accomplish what you want.
      =COUNTIF(J3:J11,">"&TODAY())

      1. Aaron:
        You could get a little fancier with:
        ="Dates Greater Than Today"&" "&"="&" "&COUNTIF(J3:J11,">"&TODAY())

        1. Aaron:
          Or finally you could put the TODAY() function in another cell and reference that cell in the formula like this:
          ="Dates Greater Than Today"&" "&"="&" "&COUNTIF(J3:J11,">"&K3)

  47. I have a problem...
    1,2,2,2,3,3,6,6,7,8,9= 7 .how to count without repeating the numbers. I mean I must count repeated value only once . I have to get result as 7 , how??

  48. i want to get formula which count same text in a list and decleared it as "good",or "bad"

  49. Hi,

    I'm currently working on trying to find out how many of my students enrolled in week 1 of my course completed and passed my class.

    I am using =countifs(class start date range, ">="&A1, class start date range, "<+"&B2, Student Status range, "completed class and should advance") I get 0 when I have 2 in my sample data that started the course within the date range and advanced.

    What am I doing wrong?

    Thanks in advance for your help!

  50. Hidears
    I have put numbers in A1 and A2 cell and put space in A3 cell and in A4 cell put, =countblank(A1:A3), it returned the value zero and later in A5 cell used =clean(A3) and why not the value in A4 cell showing one!? please help, just started learning excel..

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