Comments on: Excel COUNTIF and COUNTIFS with OR logic

As everyone knows, Excel COUNTIF function is designed to count cells based on just one criterion while COUNTIFS evaluates multiple criteria with AND logic. But what if your task requires OR logic – when several conditions are provided, any one can match to be included in the count? Continue reading →

Comments page 2. Total comments: 121

  1. I have excel sheet for result analysis range H7:ET600. I have to count "AB" from some columns {e.g. J, W, AJ, AW, BJ, BV, CC, CO, DB, DO, EB, EP columns } only. I can't select range because columns other than {J, W, AJ, AW, BJ, BV, CC, CO, DB, DO, EB, EP} these one also have "AB" entry. how I can count it?

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

      =SUM(--(A1:A10="AB"),--(C1:C10="AB"),--(E1:E10="AB"))

      Specify each column individually.

  2. How to count the occurrence of each number (range from 1 to 14) in a cell with a number pattern 1/12/5/1/13/10 or 10/11/9 or 8/5 ?

  3. Good day
    I'm trying to automate a count on a per class basis and a age.

    my current formula is this:

    =SUM(COUNTIFS('PC Spec CPT'!$A$1:$L$171, "*CLASS1*", 'PC Spec CPT'!$A$1:$L$171, {"5 years","6 years","7 years","8 years","9 years","10 years"}))

    This is returning a value of 0
    but if I do the counts separately I get the correct values. so "count class1", gives 39 and count (the different years) gives a value of 151 (as its searching the whole data set, but I only need it to count for class1 (Which is actually from A3 to L41) but I want to future proof for if I add or remove from the set

    So the count cant be 0

    1. Hi!
      The COUNTIFS formula counts the rows where all the criteria are met once. But at the same time, it cannot work with a range ($A$1:$L$171), but only with a data column ($A$1:$A$171).

    2. I think i may have figured it out...
      =SUM(COUNTIFS('PC Spec CPT'!$B$3:$B$171, "*CLASS1*",'PC Spec CPT'!$J$3:$J$171,{"5 years","6 years","7 years","8 years","9 years","10 years"}))

      Appears to give the correct result... I just needed to isolate the columns for the 2 criteria instead of doing the whole data set

  4. Can you help....?

    I have a spreadsheet with data collected from an electronically completed survey. Some of the columns are free text entry from the form. I want to summarise the submissions and categorise according to job role. However, there are a huge number of terms used for example for doctors....."registrar", "SHO", "consultant" "consultant anaesthetist", "anaesthetist", "FY2", "clinical fellow" etc. I am trying to use SUM and COUNTIFS to give me the number of forms that were submitted for a group of staff in one month. However, I am getting entries double counted as if the free text includes "consultant anaesthetist" then it is being counted twice as I want to ensure that entries that have only described the doctor as a consultant or anaesthetist are counted....

    This is my formula I have named ranges Date and Recipient, H5 and I5 are cell references to specific dates.

    =SUM(COUNTIFS(Date,">="&H$5,Date,"<"&I$5,Recipient,{"*Doctor*","*anaesthetist*","*surgeon*","*consultant*","*SAS*","*fellow*","*sho*","*reg*","*CT1*","*CT2*","*CT3*","*ST1*","*ST2*","*ST3*","*ST4*","*ST5*","*ST6*","*ST7*","*FY1*","*FY2*","associate specialist"}))

    1. Hello!
      To avoid double counting, in a separate column, write something like this SEARCH formula with all the words you need. Then use that column to count in the COUNTIFS formula.

      =--(SUM(--ISNUMBER(SEARCH({"Doctor","anaesthetist"},A2)))>0)

  5. Good morning! You all are so incredibly helpful, I just hope I can describe my issue well enough for you to understand! I have a table that looks like this:
    Virtual Virtual Virtual
    Onsite Onsite
    OTT OTT
    Jon X X X
    Dan X X X
    Rob X X X
    Tim X X X

    I need to count the number of times each person is either Onsite or OTT. The results I should get are:
    Jon 2
    Dan 1
    Rob 0
    Tim 3

    However, when I try to use normal COUNTIFS it counts Jon and Tim twice because column C has both Onsite AND OTT. I need it to count Onsite OR OTT. I tried to use the OR logic you outlined... =SUM(COUNTIFS($B$1:$G$3,{"Onsite","OTT"},B4:G4,"X")) but that doesn't work with multiple rows. How can I get this to calculate correctly? I feel like I'm so close...

    1. Okay, so multiple spaces get stripped out... Let's try with dots...
      ..........Virtual................Virtual................Virtual
      ........................Onsite...........................................Onsite
      ........................OTT....................OTT
      Jon......................X.........................X.............X
      Dan.........X.......................X......................................X
      Rob.........X.......................X...........................X
      Tim.....................X..........................X........................X

  6. I am working on a spreadsheet with Start Date, Resolve Date, and Status. I want to look at the amount of work being done in the current month. For example, if I'd like to look at the number of things completed in August, I could count everything initiated in August that has a complete status PLUS everything resolved in August with a complete status. BUT, the problem is that some things can be initiated in August and Resolved in August, which would cause the same issue to be double counted.

    My current formula for counting total complete based on start date is
    =COUNTIFS(StartDate,">="&EOMONTH(TODAY(),-1)+1,StartDate,"="&EOMONTH(TODAY(),-1)+1,Resolved,"<"&EOMONTH(TODAY(),0)+1,Status,"Completed")

    I'm not quite sure how to combine these into one formula and prevent the double counting of items that are completed with a start and resolve date in the same month. Any help would be greatly appreciated!!!

    1. Hello!
      I can't check the formula that contains unique references to your workbook worksheets. Count the records that meet the first condition, add the records that meet the second condition, and subtract the records that meet both conditions.

      =COUNTIFS(StartDate,”>= ”&EOMONTH(TODAY(),-1)+1,Status,"Completed") + COUNTIFS(Resolved,”<"&EOMONTH(TODAY(),0)+1,Status,"Completed") - COUNTIFS(StartDate,”>=”&EOMONTH(TODAY(),-1)+1, Resolved,”<"&EOMONTH(TODAY(),0)+1,Status,"Completed")

    2. I typed in two formulas, but it looks like it somehow got combined into two?? They should be as follows:

      Based on start date:
      =COUNTIFS(StartDate,">="&EOMONTH(TODAY(),-1)+1,StartDate,"="&EOMONTH(TODAY(),-1)+1,Resolved,"<"&EOMONTH(TODAY(),0)+1,Status,"Completed")

      1. I don't know why this keeps happening... There are two formulas that I'm typing in, with a new paragraph between them. My paragraph keeps getting deleted and the formulas partially combined.

  7. Hello and thank you for you dedication to helping people!

    I believe there is an issue with the AND/OR logic part of the article in that the formula generates "false positives" which must be subtracted to get a correct answer.

    LIST A LIST B LIST C LIST D
    TRUE TRUE TRUE TRUE
    TRUE TRUE TRUE FALSE
    TRUE TRUE FALSE TRUE
    TRUE TRUE FALSE FALSE
    TRUE TRUE TRUE TRUE
    TRUE TRUE TRUE FALSE
    TRUE FALSE FALSE TRUE
    TRUE FALSE FALSE FALSE
    TRUE FALSE TRUE TRUE
    TRUE FALSE TRUE FALSE
    TRUE FALSE FALSE TRUE
    TRUE FALSE FALSE FALSE

    For instance, if (A and B) or C or D are true in the above data the result should be 5 (not 7) as below:

    =COUNTIFS(LIST_A,TRUE,LIST_B,TRUE,LIST_C,TRUE)+COUNTIFS(LIST_A,TRUE,LIST_B,TRUE,LIST_D,TRUE)-COUNTIFS(LIST_A,TRUE,LIST_B,TRUE,LIST_C,TRUE,LIST_D,TRUE)

    Here's the rub. If I have additional columns (not shown and also not actual data) and need to find (A and B) or C or D or E or F or G or H or I or J or K or L in a list of 100,000 items (peak season), is an elegant solution possible?

    Thanks and I do appreciate your work!

    1. Hi!
      Your formula returns 5. But if I understood your conditions correctly, the result should be 3.
      If I understand your task correctly, the following formula should work for you:

      =SUM(--((A2:A13+B2:B13+(C2:C13+D2:D13=1)=3)))

      I hope I answered your question. If something is still unclear, please feel free to ask.

      1. Thank you for the prompt response. Unfortunately I did not clearly state the full nature of the problem.

        Data is not "true" or "false" but could be text, currency, value, date, blank, etc.
        Each line is counted only once based on conditions further explained as follows:
        List A and List B must both be "true" for a line to be counted.
        Lists E through L must have at least one condition "true" for a line to be counted.
        Each line may only be counted as one instance.
        So lines 1, 2, 3, 5, 6 should be the only lines counted for a total of 5 lines meeting the conditions.

        Helper columns discouraged because of the size of the data.

        I perceive this as Wizardry at this point.

        1. Hi!
          If I understand your task correctly, try the following formula:

          =SUM(--(((A2:A13=TRUE)*(B2:B13=TRUE)*100+(C2:C13=TRUE)+(D2:D13=TRUE)) > 100))

          1. Sir, that is a brilliant and imaginative solution! This is a truly remarkable method for implementing complex AND/OR conditions in Excel. Thank you and may the formulas be always in your favor.

  8. Hello. The "count cells with multiple sets of OR conditions" formula described above was extremely helpful for me. Thank you!

    Let's say there's a "Date_Shipped" column in D:D.

    How can I find {apples, bananas, lemons}, {bag,tray}, {delivered, in transit} ***where Date_Shipped > mm/dd/yyyy and Date_Shipped < mm/dd/yyyy***

    This is the formula from the example above I'm using and just need to figure out how to incorporate the date part:

    =SUMPRODUCT(ISNUMBER(MATCH(A2:A10,{"apples","bananas","lemons"},0))*

    ISNUMBER(MATCH(B2:B10,{"bag","tray"},0))*

    ISNUMBER(MATCH(C2:C10,{"delivered","in transit"},0)))

    Thanks for any help you can offer. Cheers!

    1. Hello!
      If you write dates in column B, then you can compare them with the desired date using the DATE function.

      =SUMPRODUCT(ISNUMBER(MATCH($A$2:$A$10, {"apples","bananas","lemons"},0))*ISNUMBER(MATCH($B$2:$B$10, {"bag","tray"},0))*ISNUMBER(MATCH($C$2:$C$10, {"delivered","in transit"},0))*(--($D$2:$D$10 > DATE(2022,2,1))*(--($D$2:$D$10 < DATE(2022,3,1)))))

      Hope this is what you need.

      1. Thank you! I was not familiar with the double minus (--). This is exactly what I needed and the formula works like a charm. CHEERS!

  9. I have a worksheet that I am working for work. Basically, I would like to count how many people visit the park (column A) in both year 2020 and 2021(column B). Column B only shows either 2020 or 2021.

    This formula: =SUM(COUNTIFS(Table2[List YR], {"2020","2021"}, Table2[COUNTY], A2))
    - provides number of people that visit on 2020 and number of people that visit on 2021, not the number of people that visit both year.

    This formula: =COUNTIFS(SPEC!A:A,A2,SPEC!B:B,2020,SPEC!B:B,2021)
    - provides 0

    Please help

    1. Hello!
      Add column C to the table with the formula

      =IF(COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,2021)>0,2021,"")

      Count the number of people using COUNTIFS:

      =COUNTIFS(B2:B10,2020,C2:C10,2021)

      I hope my advice will help you solve your task.

  10. If a student gets mark less than 16 or his average be less than 50 he must be in group D. Please write a formula for it

  11. Hello!
    My husband is trying to create a spreadsheet for work and cannot seem to find a formula that works.
    He is trying to use the COUNTIFS function to count all cells in a range that are within separate number ranges.

    He needs the cells to be counted once if they are below 517, or between 525 and 896, or above 905. Every formula we have tried has not worked. It either does not count the cells, or counts them more than once.

    This is an example of one of the formulas we have tried:

    =COUNTIFS(C3:I4,">=0","525","905")

    I know this is not right, but I have scoured the internet but have not found a solution.

    Thank you!

  12. Hey, can you help me with a google sheet formula?

    =SUM(COUNTIF('Signed Case'!C:C,{"Barry","Maya"}))

    It seems that Google Sheet can only count the total number of "Barry" but NOT BOTH "Barry" and "Maya", is there a way to fix this?

    1. =SUM(COUNTIF('Signed Case'!C:C,{"Barry","Maya"})) - Wrong Formula

      Correct one

      =SUM(COUNTIFS('Signed Case'!C:C,{"Barry","Maya"})) -than press Ctrl+shift+Enter

  13. Hello!

    Trying to find a solution for nested conditions with no success so far. Please see below:

    Spreadsheet is looking to see if a range of cells (D:32:D35) is answered "Yes", If 1-2 cells in this range are answered "Yes", then cell L:31 will be assigned a value of .5. However if greater than 2 cells in the range answer "Yes". then L:31 will need to be 1.

    Any tips? Thank you!

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

      =IF(COUNTIF(D2:D35,"Yes")<3,0.5,1)

      You can learn more about COUNTIF function in Excel in this guide.

  14. Since, after multiple attempts, I can't seem to get my formula to show correctly, can I email it? It shows fine, until I hit Send and then half of it is missing.

    1. Thank you for your emailed response, which worked.

  15. SUMPRODUCT(--(AND('Source Sheet'!$D:D>=A3,'Source Sheet'!$D:D0,1,0))

    1. Aaarrrrgghhhh!!!!!

  16. I want to count, only once, those values that match ONE of a range of three criteria. Column A contains dates and multiple rows can have the same date. Columns B, C or D contain only "Y" or "N". I want to count, for each date, only those rows where a "Y" is in any one of B, C or D. If there is an N in all three, it is not counted. For example:

    01/01/2021 Y N N
    01/01/2021 N N Y
    01/01/2021 N N N
    01/01/2021 Y Y Y
    02/01/2021 Y Y Y

    The count against 01/01/2021 should return 3 above, because there is at least one Y in cols B, C or D on three of the four rows that match that date (where there is more than one, it should only be counted once).

    No variation of SUMPRODUCT, COUNTIFS etc I have come up with gives me the right number. Can you help please?

    1. Hello!
      Please check the formula below, it should work for you:

      =SUMPRODUCT(--($A$1:$A$10=A1),IF(($B$1:$B$10="Y")+($C$1:$C$10="Y")+($D$1:$D$10="Y")>0,1,0))

      I hope this will help

      1. It does for individual dates, thank you. I also have a monthly summary sheet. In this one, months (eg Jan-21, Feb 21 etc are in column A of the summary sheet, to be checked against individual dates on column D of the source worksheet and the data to be checked for a single Y are in columns E to G of the source worksheet. I have attempted to adapt the formula you gave but this returns a VALUE error. I am guessing it is a simple missed comma or bracket, or is it the AND throwing it out?

        =SUMPRODUCT(--(AND('Source Sheet'!$D:D>=A3,'Source Sheet'!$D:D0,1,0))

        This is attempting to count, for all dates between 1st and last date of the month in column A of the target sheet, where there is a Y on each row in any one of columns E to G in the source sheet,

        For example, if A3 on the target sheet were Mar-21, it would retrieve all values where the date is greater than or equal to 1st March 2021 and less than or equal to 31st March 2021 and, for the rows between those dates in the source sheet, count how many of those rows have at least one Y between cells E and G and output that number to the cell containing the above formula in the target sheet. Note, I have ruled out using just month numbers as the source sheet contains more than one year of dates.

        1. Some of the formula was missing in my last comment. Trying again

          =SUMPRODUCT(--(AND('Source Sheet'!$D:D>=A3,'Source Sheet'!$D:D0,1,0))

  17. Hi,

    I am currently working on a formula to track 3 different types of orders and their status. In the workbook I have the order type as well as the due date and completion date in separate columns. I also created 2 columns to calculate IF statements to determine if 1) the order is "overdue" (completion date>due date) and 2) if the completion date is "blank" to mark it as outstanding. Currently I have the formula set to count any of the specific order type that is marked as overdue or as outstanding (Formula:=COUNTIFS('Due Dates'!$A:$A, T18,'Due Dates'!I:I, "OVERDUE")+COUNTIFS('Due Dates'!$A:$A, T18,'Due Dates'!J:J, "OUTSTANDING"); however, I want to narrow it down further so that only pulls the overdue+outstanding count from a specific due date instead of from everything. What function should I use to go about this? Thank you!

    1. Hello!
      If I understand your problem correctly, one more condition needs to be added to each of the COUNTIFS formulas. Like that:

      =COUNTIFS('Due Dates'!$A:$A, T18,'Due Dates'!I:I, "OVERDUE",'Due Dates'!B:B,">"&T19 )+COUNTIFS('Due Dates'!$A:$A, T18,'Due Dates'!J:J, "OUTSTANDING",'Due Dates'!B:B,">"&T19)

      where T19 is the date to count.
      Hope this is what you need.

  18. Hi,

    Help me with the count function for a number and a text on the same range. For example, I want to count cells based on attendance, if it is Y, it should be considered, if I update as 1 or 30, it should also be counted for all the data

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

      =SUM(COUNTIF(A1:A10,{1,30,"Y"}))

      I hope this will help

  19. Need help. I need to an item in a separate excel spreadsheet to be counted based on two conditions in a worksheet. I need it to count the item once if the Part No of the item (for example is 01667-1" and the order number column is populated with an all numerical number (for examples " 1234" or #1556"). The numerical number is unique except for the first number so I tried using a wild card. Oh and one more thing the numerical column (G:G) may have blanks. The count is populated into a different worksheet.
    I wrote this one but its not working. My result says 0 when I know I currently have 18 items like this....

    =COUNTIFS(' Fruit Config Flow'!$C:$C,"01667-1",' ACCG Config Flow'!$G:$G,"1*")

    Please help!!!

    1. Hello!
      The COUNTIFS function uses only range references as criteria_range. Therefore, you cannot use COUNTIFS in your case.
      Try this array formula:

      =SUM(--(FREQUENCY(IF(C2:C10=$K$1,MATCH(REPLACE(G2:G10,1,1,""), REPLACE(G2:G10,1,1,""),0)),ROW(C2:C10)-ROW(C2)+1)>0))

      This is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter.
      $K$1 is "01667-1"

      I hope I answered your question. If something is still unclear, please feel free to ask.

  20. Hello, I am trying to write a function that will count employees with either blank termination dates or termination dates after 1/1/20. The other criteria is all of these employees have to be from NYC. The function I have is =SUM(COUNTIFS(Q:Q, {"", ">1/1/2020"}, G:G, "New York")). It only seems to be counting the blanks and not adding term dates post 1/1/20 to the total count. Any advice for resolving would be very appreciated!

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

      =COUNTIFS(Q:Q, "", G:G, "New York")+COUNTIFS(Q:Q,">1/1/20", G:G, "New York")

      You can learn more about count cells with OR conditions in Excel in this article on our blog.

  21. I want to count the number of cells that have a number in them, not if it is blank.

  22. Hello! GREAT ARTICLE, but as a German Excel user there remains a crucial question:

    After

    "Count cells with 2 sets of OR conditions"

    you say:

    "Please note the semicolon in the second array constant"

    Well, semicolon is the standard in Germany for the comma (as in probably all European countries). Can somebody tell me what would be the equivalent for that semicolon then in Germany? Thanks a lot in advance.
    Thanks
    David

    1. by the way it is not comma

      1. I just got it! I downloaded one of the files on this site where the formula I meant is included - and my German Excel converted it automatically to German enterings. It would be "\" in Europe :-)

  23. Hello,

    Could you help me with a formula -- I have a pack slip for warehousing and I'm trying to sum the total of packages that have either "1" OR "13" items (cell range D10:D20) AND are being shipped via any of the following carriers "GSO", "VINGO", or "UPS" which are listed in cell range C10:C20. I currently have the formula as =SUM(COUNTIFS(C10:C20, {"GSO", "UPS", "VINGO"}, D10:D20, {"=1"; "=13"})) but it'sa not returning the correct results. Help?

    Thanks

    1. Hello!
      The formula below will do the trick for you:

      =SUM(COUNTIFS(C10:C20,{"UPS","GSO"},D10:D20,{1,13}))

      or

      =SUM(COUNTIFS(C10:C20,{"UPS","GSO"},D10:D20,{"1","13"}))
      if your numbers are written as text

  24. Hi I am trying to do the following COUNT formula:

    If cells J20:J44 are greater than 0 but less than 50

    Thank you in advance

  25. Hi,

    Can you help me with one formula? I've been struggling for a while to get it right.
    I am trying to capture (COUNTIFS) a certain employee has either not delivered a report in time (past its due date) or if he has delivered it, but past its due date. In my current raw data i have the Employee Name, Due date, the Delivery date (which can be blank while its not yet delivered, or filled if delivered) the name of the employee & the status of a report (its "draft" if its still not delivered; its "Final" or "Provisional" if it has been delivered).

    I'd highly appreciate if you could help me out. It's been bugging me for some time!

    Thanks in advance!

    1. Hello Aline!
      If you want to have the report delivery status for each employee (in Column H, for example), please use this formula:

      =IF(C2<>"",IF(B2>C2,"OK","Past due date"), "Not delivered")

      There is a due date in Column B and a delivery date (or nothing) in Column C here.
      If it is necessary to count how many reports are overdue or not submitted, please use this formula:

      =SUM((C2:C10>B2:B10)+(C2:C10=""))

      If you still have questions, please ask.

  26. Greetings,

    I have a spreadsheet that has pressure in one column from row 4 to row 54435. I have multiple pumps (the pumps have either a 0 for off or a 1 for running in each row) Each pump being in a separate column with rows 4 through row 54435. What I need to do is count a specific pressure rating range (PSI between equal to or greater than 85psi but less than 86psi). But the catch is that I want to count the pressure only once with any of the pumps running. So for example if I have pump 5 running OR pump 6 or any of the other pumps are running AND the pressure is between 85 and 86psi, I want to count it once. I would like to do this for multiple pressure settings. I have used Countif(PSIRange,">"&"="85, PSIRANGE,"<"&86,PUMP1Range,"="&1,...PumpNRange,"="&1) but this only counts if ALL the Pumps are running, not if any of the pumps are running.

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

      =SUMPRODUCT(-- (PSIRANGE>=85), -- (PSIRANGE<=86), (-- (PUMP1Range=1))+ (-- (PUMP2Range=1))+ (-- (PUMP3Range=1)))

      If there is anything else I can help you with, please let me know.

      1. Thank You

  27. Hi, I am wondering if someone can help me figure our how to combine COUNTIFS formulas. For example, I need to present this more efficiently:

    =COUNTIFS(VAR1,”Yes”,VAR2,"2016",VAR3,"Q1",VAR4,PH,VAR5,"Pres") +
    COUNTIFS(VAR1,”Yes”,VAR2,"2016",VAR3,"Q1",VAR4,"Both", VAR5,"Pres") +
    COUNTIFS(VAR1,”Yes”,VAR2,"2016",VAR3,"Q1", VAR6,PH, VAR5,"Pres") +
    COUNTIFS(VAR1,”Yes”,VAR2,"2016",VAR3,"Q1", VAR6,"Both", VAR5,"Pres")

    So I need counts if VAR1 =Yes; VAR2 = 2016; VAR3=Q1; VAR5=Pres; AND VAR4= PH or Both; AND VAR6=PH or Both. I think the way I have it does the job, but I'm sure there is a more concise way to write the formula as it may get very long as I add criteria to it.

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

      =SUMPRODUCT(--(A1:A4="Yes"),--(B1:B4=2016), --(C1:C4="Q1"), ((D1:D4="PH")+(D1:D4="Both")),--(E1:E4="Pres"), ((F1:F4="PH")+(F1:F4="Both")))

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

      1. I am having the same problem like "E Safi" but rather than using direct literal text. I need to use wild card as it is group in a single cell. below is a working formula but the problem is it counts the cell twice if it contains both the word "PH" and "Both". is there anyways that it will be only counted once?
        =SUMPRODUCT(--(A1:A4="Yes"),--(B1:B4=2016),--(C1:C4="Q1"),((D1:D4="PH")+(D1:D4="Both")),--(E1:E4="Pres"),(ISNUMBER(SEARCH("*PH*",F1:F4)+ISNUMBER(SEARCH("*Both*",F1:F4)))))

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

          =SUMPRODUCT(--(A1:A4="Yes"),--(B1:B4=2016), --(C1:C4="Q1"),((D1:D4="PH")+(D1:D4="Both")), --(E1:E4="Pres"), (ISNUMBER(SEARCH("*PH*",F1:F4))+ISNUMBER(SEARCH("*Both*",F1:F4))))

          I hope it’ll be helpful.

  28. Thanks so much for this tutorial, it was hugely helpful and very easy to follow!

    I found that I didn't need SUM for the leave chart I created counting letters in a column. I just used:

    {=COUNTIF(C$6:C$56,$AW$59:$AW$66)}

    And it gave me the correct summed number. Worked in 2016 and 2010.

    1. Nope, definitely need SUM!

  29. I know this is a long shot however, I am currently having issues with creating a formula to read the following.
    Name score1 score2 score3
    smith 58 55 61

    in the example above I need to create a formula that will count the number of cells w/ a score below 60. however, this issue I am having is I need the formula to only count this as a multiple failed event by one person and not read the formula as two cells <60, if that makes sense.
    I have tried creating multiple formulas and cannot seem to get this to only count as one multiple failed event for the individual.

    1. Some examples of formula I have been using are
      =countif(E198:E262,"<60",G198:G262,"<60",I198:I262,"<60"). This formula only seems to count every score below 60. I need the formula to count multiple scores under 60 as 1 multiple failed event.

      1. Hi Brandon,

        And if a certain person has one or more scores under 60, what do you want to return? Simply 1, or something else?

  30. Hi,

    I may be under the wrong impression but, technically, all of your suggested solutions seem to work only as an AND fuction. That being said, your suggestions don't work well with my need of the real OR function in countif formula. Allow me to explain my problem.

    I have a list of phrases in column A, position numbers in column B and another position numbers in column C. Now, I need to count all the phrases in column A that have the position <10 either in B or in C. The thing is that some phrases have both position numbers <10 and thus are being counted twice when using your solutions. That is why I need OR, not AND.

    Surely, I could use filters to list and count those phrases "manually", but I need to make this process automated, as I am making basically the whole excel fill itself up based on my edits in one single sheet.

    Thank you for your future suggestions.

    1. Hi Jan,

      A simple solution that comes to mind is to create a helper column (you can hide it or move to the end of the worksheet) and pull the smaller of the 2 values there: =MIN(B2:C2)

      And then, you use COUNTIF on the helper column, e.g.=COUNTIF(D2:D100,"<"&10)

      1. Thanks
        many many love to you. This trick was really awesome.

  31. Can you help me with a formula?

    I have some student test scores and I'd like to create a formula that tells me how many students in each class got above the year average, and how many got below. But, there are some absent students, so I'd like to only count the scores above 0.

    This is my current formula for above average scorers in class 1: =COUNTIF(AD7:AD45,">"&$AD$287)

    This is my current formula for below average scorers in class 1:
    =COUNTIF(AD7:AD45," =AVERAGEIF(AD7:AD285,"0") ]*

    These formulas work, but the below average formula also includes scores for students who were absent and didn't take the test (so they include 0 scores and I'd like to change it).

    My guess was that I should include COUNTIF(AD7:AD45,""&"0") somewhere in the formula, but every way I try isn't working. Do I need to do something different, like some sort of OR condition?

    Thank you in advance,

    Jonathan

    1. Jonathan:
      You might be overthinking this.
      Try using:=COUNTIF(AD7:AD45,">0")

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