# Formula for COUNTIFS combined with OR (I think)

Copper Contributor

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

11 Replies

# Re: Formula for COUNTIFS combined with OR (I think)

=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?

# Re: Formula for COUNTIFS combined with OR (I think)

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

``= COUNTIFS(Priority, "High", Status, "<>Complete")``

# Re: Formula for COUNTIFS combined with OR (I think)

Sorry, the forum was showing 0 responses. Still, good to know we are in agreement!

# Re: Formula for COUNTIFS combined with OR (I think)

@OliverScheurichThank you so much!  That worked a treat!

# Re: Formula for COUNTIFS combined with OR (I think)

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!

# Re: Formula for COUNTIFS combined with OR (I think)

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.

# Re: Formula for COUNTIFS combined with OR (I think)

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"}))

# Re: Formula for COUNTIFS combined with OR (I think)

= 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?

# Re: Formula for COUNTIFS combined with OR (I think)

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"}))

# Re: Formula for COUNTIFS combined with OR (I think)

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!

# Re: Formula for COUNTIFS combined with OR (I think)

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!