Forum Discussion
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 label...
=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?
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"}))
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"}))
- It looks like the first line counts old, unfinished cases and the following lines count current cases.
I think you just have to count cases with dates less than or equal to Today()+6
=COUNTIFS($H$9:$H83,"<="&TODAY()+6,$J$9:$J83,{"Not Started","In Progress"}))- Thank you very much Richard_Wendt. Unfortunately, the formula produced a "spill" error. However, the formula provided by Oliver has worked, so all is good. I appreciate you helping!
= 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?
- Thank you very much OliverScheurich. The formula (2nd one) worked, I didn't try the first. It doesn't capture entries with dates <TODAY, however, that has worked out better as I have overdue captured separately. Really appreciate your help again!
