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

1. ## 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

2. ## 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

3. ## 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.

4. ## 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. ## Re: Use of #Min#Countif#Time Sorting Required.

Any solutions??

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

means I did not understand what you want?

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

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

8. ## 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.

9. ## 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.

10. ## 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

11. ## 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.

12. ## 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. ## 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

14. ## 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.

15. ## 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.

Page 2 of 3 First 1 2 3 Last

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

#### 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