Forum Discussion

AsimA335's avatar
AsimA335
Copper Contributor
Aug 09, 2024

Formula for COUNTIFS combined with OR (I think)

Hello,

 

Please can someone help me with a formula for the following:

 

Column E is labelled "Priority" with drop down options of "High", "Medium" or "Low" from a lookup table.

Column I is labelled "Status" with drop down options of "Complete", "In Progress" or "Not Started" from a look up table.

 

I want to count in cell I2, how many priorities in column E are "high" AND column I has the status "In Progress" OR "Not Started" [i.e. a status that is not "Complete"].

 

I have created a COUNTIFS formula but cannot seem to apply a second criteria that excludes from the count any with the status "Complete": =(COUNTIFS($E7:E13,"High"))

 

I can then adjust the same formula for "Medium" and "Low" priorities for cells I3 and I4 respectively.

 

 

  • AsimA335 

    =SUM(COUNTIFS($E$7:$E$13,"high",$I$7:$I$13,{"In progress","Not started"}))

    =SUMPRODUCT(($E$7:$E$13="high")*(($I$7:$I$13="In progress")+($I$7:$I$13="Not started")))

     

    Does any of these formulas return the intended result?

    • AsimA335's avatar
      AsimA335
      Copper Contributor
      Thanks again OliverScheurich!
      I tried to use your formula to create a new formula for a different value.
      This time, I'm trying to count how many tasks are due this week based on a date in column H, and on a status in column I, that is either "In Progress" or "Not Started" .

      Here is what I've come up with but it is not working. Perhaps I need to simplify it to use "<>complete" as status as per Peter's suggestion below. My columns have names but there is content above the table that possibly prevents Excel identifying the names:

      =SUM(COUNTIFS($H$9:$H83,"<"&TODAY(),$J$9:$J83,{"Not Started","In Progress"})) + (COUNTIFS($H$9:$H83,TODAY(),$J$9:$J83,{"Not Started","In Progress"})) + (COUNTIFS($H$9:$H83,TODAY+1(), $J$9:$J83, {"Not Started","In Progress"})) + (COUNTIFS($H$9:$H83,TODAY+2(),$J$9:$J83,{"Not Started","In Progress"})) + (COUNTIFS($H$9:$H83,TODAY+3(),$J$9:$J83,{"Not Started","In Progress"})) + (COUNTIFS($H$9:$H83,TODAY+4() ,$J$9:$J83,{"Not Started","In Progress"})) + (COUNTIFS($H$9:$H83,TODAY+5(),$J$9:$J83,{"Not Started","In Progress"})) + (COUNTIFS($H$9:$H83,TODAY+6(),$J$9:$J83,{"Not Started","In Progress"}))
      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor

        AsimA335 

        = SUMPRODUCT( (COUNTIFS($H$9:$H83,"="&TODAY(),$J$9:$J83,{"Not Started";"In Progress"}))
        + (COUNTIFS($H$9:$H83,"="&TODAY()+1, $J$9:$J83, {"Not Started";"In Progress"}))
        + (COUNTIFS($H$9:$H83,"="&TODAY()+2,$J$9:$J83,{"Not Started";"In Progress"}))
        + (COUNTIFS($H$9:$H83,"="&TODAY()+3,$J$9:$J83,{"Not Started";"In Progress"}))
        + (COUNTIFS($H$9:$H83,"="&TODAY()+4,$J$9:$J83,{"Not Started";"In Progress"}))
        + (COUNTIFS($H$9:$H83,"="&TODAY()+5,$J$9:$J83,{"Not Started";"In Progress"}))
        + (COUNTIFS($H$9:$H83,"="&TODAY()+6,$J$9:$J83,{"Not Started";"In Progress"})) )

         

        Or:

        =SUMPRODUCT((COUNTIFS($H$9:$H83,">="&TODAY(),$H$9:$H83,"<="&TODAY()+6,$J$9:$J83,{"Not Started";"In Progress"})))

         

        Does any of this formulas return the intended result? 

         

         

  • AsimA335 

    Assuming the columns of you table are named (or you could use structured references)

    = COUNTIFS(Priority, "High", Status, "<>Complete")
    • AsimA335's avatar
      AsimA335
      Copper Contributor
      Thanks Peter! I think this will count completed items however I'm looking to count not started and in progress items. But I can use a combo of your suggestions and Oliver's suggestions for a clean formula. There are other tables crossing into the same column, so I think a cell-specifc formula may be best rather than a column-specific formula. However, this is great to know for future spreadsheets!
      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor

        AsimA335 

        Just to clarify the syntax, the status

        "<>complete"

        means the formula returns a count for items that are not complete.  That is because of the "<>" not equal to.

Resources