Count require..Based on Start time

V

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,284
Office Version
  1. 365
Platform
  1. Windows
On Sheet1
Code: 
[TABLE="width: 299"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Department Name[/TD]
[TD]12:00 AM[/TD]
[TD]1:00 AM[/TD]
[TD="align: right"]2:00 AM[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
....and so on till 11.00 pm

On Sheet2, I have Start time data
Code: 
[TABLE="width: 159"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Shift Start Time (24 hour format[/TD]
[/TR]
[TR]
[TD]13:30[/TD]
[/TR]
[TR]
[TD]13:30[/TD]
[/TR]
[TR]
[TD]13:30[/TD]
[/TR]
[TR]
[TD]13:30[/TD]
[/TR]
[TR]
[TD]22:30[/TD]
[/TR]
[TR]
[TD]22:30[/TD]
[/TR]
[TR]
[TD]13:30[/TD]
[/TR]
[TR]
[TD]22:30[/TD]
[/TR]
[TR]
[TD]13:30[/TD]
[/TR]
[TR]
[TD]13:30[/TD]
[/TR]
[TR]
[TD]22:30[/TD]
[/TR]
[TR]
[TD]13:30[/TD]
[/TR]
[TR]
[TD]13:30[/TD]
[/TR]
[TR]
[TD]22:30[/TD]
[/TR]
</tbody>[/TABLE]

I need count of respective time in Sheet 1. If Start time is 00.55, then count of this goes in "12.00 AM"..

Any one provide this formula..
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
In [Sheet1] you have DEPARTMENT listed.

In [Sheet2], there is no department information.

Do your worksheets represent department (i.e. Sheet1 = Department A... Sheet2 = Department B.. and so on)

OR..

Should there be data for the DEPARTMENT in [Sheet2], listed alongside the [Shift Start Time (24 hour format)] field?

OR..

Something else.

Put another way.. In the table in [Sheet1] where you've listed out the DEPARTMENTS.. where does that information come from - how do we match up the aggregated data against the rows?


When you say "COUNT" do you mean it would appear like this:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Department[/TD]
[TD]Shift Start Time[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]00:55[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]00:58[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]01:30[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]00:23[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 500"]
<tbody>[TR]
[TD]Department[/TD]
[TD]12:00am[/TD]
[TD]01:00am[/TD]
[TD]02:00am[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thanks for revert..

In [Sheet2], there is no department information. = it is there..in Col E. And "Start Time" in Col G.

Do your worksheets represent department (i.e. Sheet1 = Department A... Sheet2 = Department B.. and so on) = NO

OR..

Should there be data for the DEPARTMENT in [Sheet2], listed alongside the [Shift Start Time (24 hour format)] field? = YES, as said above.



Put another way.. In the table in [Sheet1] where you've listed out the DEPARTMENTS.. where does that information come from - how do we match up the aggregated data against the rows? = Sheet 1 tabular format is fixed..


When you say "COUNT" do you mean it would appear like this:

Yes....I require in this format..

[TABLE="width: 500"]
<tbody>[TR]
[TD]Department[/TD]
[TD]12:00am[/TD]
[TD]01:00am[/TD]
[TD]02:00am[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/QUOTE]

Using CountIfs we can do that..I tried also. But hw come it will display the output?
The reason B'coz, In sheet1 my tabular format is like this-

Code: 
[TABLE="width: 479"]
<tbody>[TR]
[TD]Department Name[/TD]
[TD]12:00 AM[/TD]
[TD]1:00 AM[/TD]
[TD="align: right"]2:00 AM[/TD]
[TD="align: right"]3:00 AM[/TD]
[TD="align: right"]4:00 AM[/TD]
[TD="align: right"]5:00 AM[/TD]
[/TR]
[TR]
[TD]HR[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]IT[/TD]
[TD]Whatever[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FINANCE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
small note..in above example, i changed department names A, B, C to HR, IT, FINANCE resp.

Formula I used, for your reference..on sheet1
Code: 
=COUNTIFS(Sheet2!E2:E97,Sheet1!A4,Sheet2!G2:G97,Sheet1!B2)
where,
E2:E97 = Department Names
Sheet1!A4 = "HR"
Sheet1!B2 = "12:00 AM"
 
Last edited:
Upvote 0
VBABEGINER said:
[Sheet2] department information = Col E And "Start Time" in Col G.


Yes....I require in this format..

[TABLE="width: 500"]
<tbody>[TR]
[TD]Department[/TD]
[TD]12:00am[/TD]
[TD]01:00am[/TD]
[TD]02:00am[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...



Try this: https://www.dropbox.com/s/8jrfm5gtwkbxgli/SumProductForRangeOfTimes.xlsx?dl=0
 
Upvote 0
VBABEGINER said:
This site is not opening at my end..security reason..
Click to expand...


Use this:



Excel 2010
ABCD
Department

<tbody>
[TD="align: center"]3[/TD]

[TD="align: center"]00:00[/TD]
[TD="align: center"]01:00[/TD]
[TD="align: center"]02:00[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #D9D9D9"]HR[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #D9D9D9"]IT[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #D9D9D9"]FINANCE[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B4[/TH]
[TD="align: left"]=SUMPRODUCT(--(Sheet2!$E$1:$E$5000 = $A4),--(Sheet2!$G$1:$G$5000 >= B$3),--(Sheet2!$G$1:$G$5000 < C$3<c$3< font="">)</c$3<>)[/TD]
[/TR]
[TR]
[TH][/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Excel 2010
EFG
Dept
HR
IT
FINANCE
FINANCE
HR
HR
FINANCE
HR
IT
IT
IT
FINANCE
HR
HR

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: center"]StartTime[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: center"]00:10[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: center"]13:30[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: center"]13:30[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: center"]13:30[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: center"]22:30[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: center"]22:30[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: center"]13:30[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: center"]15:30[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: center"]13:30[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: center"]13:30[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: center"]22:30[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: center"]13:30[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: center"]13:30[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: center"]22:30[/TD]

</tbody>



Sheet2


​
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

After you've entered the formula into B4, copy and paste it to the relevant cells.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
TIME LIMITATIONS
Replies
1
Views
72
cooleomter
C
B
  • Solved
Possible to pull apart 1st section of string for date & time
2 3
Replies
23
Views
455
Peter_SSs
Peter_SSs
A
  • Question Question
Get enddatetime if X no. of working hours is added to startdatetime
Replies
1
Views
439
myall_blues
myall_blues
K
  • Question Question
Super Time sheet
Replies
1
Views
528
kolekbolek1283
K
tezza
  • Solved
Autosum specific rows and columns
Replies
2
Views
271
tezza
tezza

Forum statistics

Threads
1,223,969
Messages
6,175,680
Members
452,667
Latest member
vanessavalentino83

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top