+ Reply to Thread
Results 1 to 18 of 18

COUNTIFS is not able to solve the task

  1. #1
    Registered User
    Join Date
    01-14-2019
    Location
    Split
    MS-Off Ver
    Office 2021
    Posts
    11

    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
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

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


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    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<>""))
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  4. #4
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    2,132

    Re: COUNTIFS is not able to solve the task

    Another way,

    Please Login or Register  to view this content.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: COUNTIFS is not able to solve the task

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

  6. #6
    Registered User
    Join Date
    01-14-2019
    Location
    Split
    MS-Off Ver
    Office 2021
    Posts
    11

    Re: COUNTIFS is not able to solve the task

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

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: COUNTIFS is not able to solve the task

    Thread moved.

    What is your Excel version?

  8. #8
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    2,132

    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. #9
    Registered User
    Join Date
    01-14-2019
    Location
    Split
    MS-Off Ver
    Office 2021
    Posts
    11

    Re: COUNTIFS is not able to solve the task

    Excel 2021 v2402

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: COUNTIFS is not able to solve the task

    Please update your forum profile accordingly. Do this NOW.

  11. #11
    Registered User
    Join Date
    01-14-2019
    Location
    Split
    MS-Off Ver
    Office 2021
    Posts
    11

    Re: COUNTIFS is not able to solve the task

    Thanks for your help. I will try.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: COUNTIFS is not able to solve the task

    You have done it - thanks.

  13. #13
    Registered User
    Join Date
    01-14-2019
    Location
    Split
    MS-Off Ver
    Office 2021
    Posts
    11

    Re: COUNTIFS is not able to solve the task

    Quote Originally Posted by windknife View Post
    Another way,

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

  14. #14
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    2,132

    Re: COUNTIFS is not able to solve the task

    You are welcome.

  15. #15
    Registered User
    Join Date
    01-14-2019
    Location
    Split
    MS-Off Ver
    Office 2021
    Posts
    11

    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.
    Attached Files Attached Files
    Last edited by vilipec; 03-29-2024 at 02:24 PM.

  16. #16
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,848

    Re: COUNTIFS is not able to solve the task

    Try

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  17. #17
    Registered User
    Join Date
    01-14-2019
    Location
    Split
    MS-Off Ver
    Office 2021
    Posts
    11

    Re: COUNTIFS is not able to solve the task

    Dear JohnTopley,

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

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: COUNTIFS is not able to solve the task

    Add the bit in red:

    =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<>"")))
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Trying to solve my problem with COUNTIFS or other formula
    By Sultan1994 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-21-2022, 03:12 AM
  2. [SOLVED] the way we solve Solve equation. Can Excel solve this?
    By tta.akmal in forum Excel General
    Replies: 3
    Last Post: 08-10-2020, 07:49 AM
  3. [SOLVED] Pivot Chart: Task spend over time, +comparison w/ Task Budget
    By mike_302 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 12-28-2018, 04:21 PM
  4. export excel list of task in custom outlook task 2010.
    By maxseal in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-22-2014, 03:51 PM
  5. COUNTIFS AND OR or some other magic trick that will solve this issue...
    By lumberjim in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-15-2013, 02:28 AM
  6. [SOLVED] IF contingent task closed, THEN change formatting of dependent task cell
    By tek_9 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-29-2012, 08:40 PM
  7. Make 200 task: can you help me work out a formula to solve this quiz?
    By Alex Brown in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 11-02-2009, 08:04 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1