SOLVED

Countif with excluding condition

Copper Contributor

Hello
I am trying to get the total of days in a range of cells where the start time is later than 06:30 but I need to exclude the time if there is "Course* in another range os cells. I am using this formula:

=COUNTIF(E3:E9,">06:30:00") -SUMPRODUCT(COUNTIF(F3:F9,"Course"))

It is working when I have time after 06:30, but it comes negative result if I have 06:30 time and Course happening at the same time. Not sure if that makes sense.

Please I really would appreciate any help! Thanks!

4 Replies
best response confirmed by Elise0520 (Copper Contributor)
Solution

@Elise0520 

=COUNTIFS(E3:E9,">06:30:00",F3:F9,"<>Course")

Maybe this is what you are looking for.

countifs.JPG

 

@Elise0520 

Or

=SUMPRODUCT( (E3:E9>=TIME(6,30,0) )*(F3:F9<>"Course") )
Yes! It works now! Thank you very much! ;)
Hi. I've already got the answer. Thank you very much!
1 best response

Accepted Solutions
best response confirmed by Elise0520 (Copper Contributor)
Solution

@Elise0520 

=COUNTIFS(E3:E9,">06:30:00",F3:F9,"<>Course")

Maybe this is what you are looking for.

countifs.JPG

 

View solution in original post