+ Reply to Thread
Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 16 to 30 of 34

Use of #Min#Countif#Time Sorting Required.

  1. #16
    Registered User
    Join Date
    08-01-2017
    Location
    Pakistan
    MS-Off Ver
    2010
    Posts
    24

    Re: Use of #Min#Countif#Time Sorting Required.

    Sir, reattached the file.
    Need to check that how many cakes were made hourly by each worker. Thanks
    Attached Files Attached Files
    Last edited by AliGW; 03-23-2018 at 12:50 PM. Reason: Unnecessary quotation removed.

  2. #17
    Forum Guru nflsales's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2003; 2007
    Posts
    6,422

    Re: Use of #Min#Countif#Time Sorting Required.

    j5=COUNTIFS(Sheet2!$C$3:$C$2792,$B5,Sheet2!$D$3:$D$2792,">="&INT($D5)+J$4,Sheet2!$D$3:$D$2792,"<"&INT($D5)+J$4+TIME(1,0,0))
    Try this and copy across
    Click *Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved) If you are satisfied and add reputation the for the one who helps you

    Regards

    Samba

  3. #18
    Registered User
    Join Date
    08-01-2017
    Location
    Pakistan
    MS-Off Ver
    2010
    Posts
    24

    Re: Use of #Min#Countif#Time Sorting Required.

    Thanks Sir, it works. But one question that in Total Cakes column the formula is "=COUNTIF(Sheet2!$C$3:$C$2792, B5) - 1" why is it - 1 as this results in one lesser . Plz explain.
    Last edited by AliGW; 03-23-2018 at 12:50 PM. Reason: Unnecessary quotation removed.

  4. #19
    Forum Guru nflsales's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2003; 2007
    Posts
    6,422

    Re: Use of #Min#Countif#Time Sorting Required.

    I am also have the same doubt when I saw your excel file
    What I think was
    for Urban BA Sialkot 5
    06-03-2018 11:00:00 is Starting Time of Cake One and
    06-03-2018 11:38:00 is ending time of Cake one at the same time Beginning time of Cake two
    if Urban BA Sialkot 5 for 5 Times the cakes were 4, if the times were 6 the cakes were 5 and soon.....
    This is what I thought

  5. #20
    Registered User
    Join Date
    08-01-2017
    Location
    Pakistan
    MS-Off Ver
    2010
    Posts
    24

    Re: Use of #Min#Countif#Time Sorting Required.

    Any solutions??
    Last edited by AliGW; 03-23-2018 at 12:49 PM. Reason: Unnecessary quotation removed.

  6. #21
    Forum Guru nflsales's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2003; 2007
    Posts
    6,422

    Re: Use of #Min#Countif#Time Sorting Required.

    means I did not understand what you want?

  7. #22
    Registered User
    Join Date
    08-01-2017
    Location
    Pakistan
    MS-Off Ver
    2010
    Posts
    24

    Re: Use of #Min#Countif#Time Sorting Required.

    Quote Originally Posted by nflsales View Post
    means I did not understand what you want?
    How do i remove this - 1 ?

  8. #23
    Forum Expert
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    5,420

    Re: Use of #Min#Countif#Time Sorting Required.

    Since Samba's formula matched your manually input results and since you are already summing J5:W5 in X5, perhaps you could populate C5 using: =X5
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  9. #24
    Registered User
    Join Date
    08-01-2017
    Location
    Pakistan
    MS-Off Ver
    2010
    Posts
    24

    Re: Use of #Min#Countif#Time Sorting Required.

    Friends, I have attached file. Would like to have following results.
    Just like Sheet 1 in which, from I column to Y column i get result how many cakes were made during Clock hours. Same like this I would like to
    get results in Sheet 4 from taking data from Sheet 2. That how many cakes were made in 1 min, 2 min , 3 min and so on.
    For example
    if one cake is made at 1:00 AM the second one is made 1:03 AM then 2nd cake time is 3 min. file is attached. Please check.
    Attached Files Attached Files

  10. #25
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,520

    Re: Use of #Min#Countif#Time Sorting Required.

    Try

    Sort Shhet2 by Names then Time

    in E4

    ==IF(C4<>C3,"",D4-D3)

    copy down

    in Sheet4

    in H5

    =COUNTIFS(Sheet2!$C:$C,'Sheet 4'!$B5,Sheet2!$E:$E,"<=" & TIME(0,0,30))

    Similar formula for other cells
    Attached Files Attached Files

  11. #26
    Registered User
    Join Date
    08-01-2017
    Location
    Pakistan
    MS-Off Ver
    2010
    Posts
    24

    Re: Use of #Min#Countif#Time Sorting Required.

    Sir, I have done as you instructed, but the results are not as accepted. As In Time sheet, Column D, Total cakes are shown but as I have calculated
    total time cake the total count dose not match (Column AF & AG). Plz check.
    Further more in sheet 4, I need to this result.
    If a i Put any Value in E4(For example 1, which means 1 min) the below cell against each name should show those cakes which are made within 1 min.
    secondly in Column F, if i put any value in F4 (For example 4, which means 4 min) the below cells against each name should show those cakes which took more time then 4 min.
    Plz check.
    File is attached.
    Attached Files Attached Files
    Last edited by AliGW; 03-23-2018 at 12:49 PM. Reason: Unnecessary quotation removed.

  12. #27
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,520

    Re: Use of #Min#Countif#Time Sorting Required.

    You did change these formula to allow for HOURS in AB onward: they only address the MINUTES

    =COUNTIFS(Sheet2!$C:$C,$B5,Sheet2!$E:$E,">" & TIME(0,COLUMNS($J:AA),0),Sheet2!$E:$E,"<=" & TIME(0,COLUMNS($J:AB),0))

    so change as required.

  13. #28
    Registered User
    Join Date
    08-01-2017
    Location
    Pakistan
    MS-Off Ver
    2010
    Posts
    24

    Re: Use of #Min#Countif#Time Sorting Required.

    othing Change sir, I have tried.

    Further more in sheet 4, I need to this result.
    If a i Put any Value in E4(For example 1, which means 1 min) the below cell against each name should show those cakes which are made within 1 min.
    secondly in Column F, if i put any value in F4 (For example 4, which means 4 min) the below cells against each name should show those cakes which took more time then 4 min.
    Plz check.
    File is attached
    Last edited by AliGW; 03-23-2018 at 12:49 PM. Reason: Unnecessary quotation removed.

  14. #29
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,520

    Re: Use of #Min#Countif#Time Sorting Required.

    If I change formulae in AB onward the totals equal those in column C: I don't now why you added 1 in column D

    You need to show expected results for first few rows and identify the problem as the formulae are "correct" as far as I can see.

    See AB5 in attached.

    I will leave you TRY and answer you second requirement and need some expected results.
    Attached Files Attached Files

  15. #30
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    19,181

    Re: Use of #Min#Countif#Time Sorting Required.

    Alvi-gee - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

+ Reply to Thread
Page 2 of 3 FirstFirst 1 2 3 LastLast

Thread Information

Users Browsing this Thread

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

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