+ Reply to Thread
Results 1 to 10 of 10

Countif Formula

  1. #1
    Registered User
    Join Date
    06-17-2013
    Location
    cairo,egypt
    MS-Off Ver
    Excel 2010
    Posts
    20

    Question Countif Formula

    Hi All,

    Appreciate your support with a countif formula to count the number of agents scheduled for breaks in every 30min interval as attached
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,330

    Re: Countif Formula

    Maybe something like:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    06-17-2013
    Location
    cairo,egypt
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Countif Formula

    Thank you TMS for your reply.

    Please consider that we would like to count the number of agents starting for 6:00 AM who are scheduled for breaks

    Attachment 679364

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,330

    Re: Countif Formula

    I don't know where you indicated that as a requirement.

    Did the formula provided answer your initial question?

    You attachment is invalid.

  5. #5
    Registered User
    Join Date
    06-17-2013
    Location
    cairo,egypt
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Countif Formula

    Considering that the aim from the report is to count the number of agents scheduled for breaks in every interval starting from 6:00 AM
    the previous formula was not serving the requested data.

    Apologies for the corrupted attached.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,093

    Re: Countif Formula

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  7. #7
    Registered User
    Join Date
    06-17-2013
    Location
    cairo,egypt
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Countif Formula

    Thank you AliGW

    Please find the attached report
    Attached Files Attached Files

  8. #8
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    473

    Re: Countif Formula

    Hi wael_afifi,
    I have added the formula provided by TMS into your sample file into cell B11 and pulled through to CH11. The formula works fine.

    There is a problem with your 'break interval' values from cell AM10 onwards. AL10 = 12:00:00 AM AM10 = 1/01/1900 12:30:00 AM ... You must remove the date from the time.

    Why do you have 'break intervals' covering more than 24 hours. The first agent is starting at 5 AM, the last at 6 PM finishing at 3 AM. No one is working between 3 AM and 5 AM. Your 'break interval' can stop after AQ10.
    Trust this helps.

  9. #9
    Registered User
    Join Date
    06-17-2013
    Location
    cairo,egypt
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Countif Formula

    Thank you so much @TMS for providing the formula
    And thank you @ORoos for the clarifications.

    @ROoos,
    After updating the highlighted mistakes in my report as mentioned.
    I have one question if i may:
    Now after applying the formula i can calculate the number of agents who are scheduled for breaks in the 6:00 AM interval in B11 and so on, right?

  10. #10
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    473

    Re: Countif Formula

    Hi wael_afifi,
    Yes TMS's formula is counting the number of agents scheduled for a break for each of the half hour blocks starting from 6AM onwards in row 11.
    You have a total of 249 breaks in your sample data. The formula is picking up 249 breaks, hence balancing back.

    You have 0 agents on breaks between 6:00 - 6:30 (they just started)
    2 between 6:30 - 7:00
    2 between 7:00 - 7:30 . . .
    2 between 9:00 - 9:30
    8 between 9:30 - 10:00 etc.

+ 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. [SOLVED] formula to identify the unique code after filtration using countif or other formula
    By felixpanganiban in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-27-2019, 01:25 PM
  2. [SOLVED] COUNTIF formula where part of the range reference needs to use the result of a formula
    By zookeepertx in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 11-09-2018, 05:39 PM
  3. Adding a COUNTIF function to an existing IF AND COUNTIF Formula...
    By Ourkid123uk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-24-2018, 10:09 AM
  4. Assistance with CountIF formula and additional formula conditions
    By solios in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-18-2016, 01:46 PM
  5. Faster if/countif formula for flagging uniques - countif too slow
    By Speshul in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-19-2015, 01:44 PM
  6. [SOLVED] =Subtotal(countif,Range); Subtotal and countif in 1 formula [SOLVED]
    By thomas.mapua in forum Excel General
    Replies: 5
    Last Post: 01-06-2012, 11:33 AM
  7. Adding a CountIF to a formula that is already Countif
    By Cmorgan in forum Excel General
    Replies: 4
    Last Post: 06-01-2011, 09:34 AM

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