# COUNTIFS is not able to solve the task

1. ## COUNTIFS is not able to solve the task

Hello.
In cell B15, I need a formula that will count the employees who worked together in a given period in one workday (see attachment).
Best regards

2. ## Re: COUNTIFS is not able to solve the task

Are you still using Excel 2010? Why have you posted in the VBA section if you want a formula? Shall I move the thread for you?

3. ## Re: COUNTIFS is not able to solve the task

Try this:

=SUMPRODUCT(((B\$4:B\$11<=C13)*(C\$4:C\$11>B13)+((B\$4:B\$11<=C13)+(C\$4:C\$11>B13))*(C\$4:C\$11<B\$4:B\$11))*(B\$4:B\$11<>""))

4. ## Re: COUNTIFS is not able to solve the task

Another way,

``Please Login or Register  to view this content.``

5. ## Re: COUNTIFS is not able to solve the task

Windknife, yours breaks down if there are times crossing midnight.

6. ## Re: COUNTIFS is not able to solve the task

Sorry. My mistake, You can switch to the formula section. Thank you.

8. ## Re: COUNTIFS is not able to solve the task

Glenn, You are right, my formula doesn't consider times crossing midnight.

Nevertheless, OP's data doesn't happen this situation.

9. ## Re: COUNTIFS is not able to solve the task

Excel 2021 v2402

11. ## Re: COUNTIFS is not able to solve the task

Thanks for your help. I will try.

12. ## Re: COUNTIFS is not able to solve the task

You have done it - thanks.

13. ## Re: COUNTIFS is not able to solve the task

Originally Posted by windknife
Another way,

``Please Login or Register  to view this content.``
Thanks for your help. I will try.

14. ## Re: COUNTIFS is not able to solve the task

You are welcome.

15. ## Re: COUNTIFS is not able to solve the task

Dear Glenn Kenned, dear "windknif"

The problem in both previous formulas is in the counting as soon as the start time is entered. Formulas are in E15 and E16.

16. ## Re: COUNTIFS is not able to solve the task

Try

Formula:
`Please Login or Register  to view this content.`

17. ## Re: COUNTIFS is not able to solve the task

Dear JohnTopley,

the formula is not valid in the case as in the attachment

18. ## Re: COUNTIFS is not able to solve the task

=IF(COUNTA(\$C\$4:\$C\$11)=0,0,SUMPRODUCT(((B\$4:B\$11<=C13)*(C\$4:C\$11>B13)+((B\$4:B\$11<=C13)+(C\$4:C\$11>B13))*(C\$4:C\$11<B\$4:B\$11))*(B\$4:B\$11<>"")*(\$C\$4:\$C\$11<>"")))

There are currently 1 users browsing this thread. (0 members and 1 guests)