Count require..Based on Start time

[VBABEGINER]

On Sheet1

[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

[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..

 

[MartyS]

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:

Department	Shift Start Time
A	00:55
A	00:58
A	01:30
B	00:23

<tbody>
</tbody>

Department	12:00am	01:00am	02:00am
A	2	1	0
B	1
C

<tbody>
</tbody>

 

[VBABEGINER]

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

Department	12:00am	01:00am	02:00am
A	2	1	0
B	1
C

<tbody>
</tbody>

[/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-

[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

=COUNTIFS(Sheet2!E2:E97,Sheet1!A4,Sheet2!G2:G97,Sheet1!B2)

where,
E2:E97 = Department Names
Sheet1!A4 = "HR"
Sheet1!B2 = "12:00 AM"

 

[MartyS]

[Sheet2] department information = Col E And "Start Time" in Col G.


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

Department	12:00am	01:00am	02:00am
A	2	1	0
B	1
C

<tbody>
</tbody>

Try this: https://www.dropbox.com/s/8jrfm5gtwkbxgli/SumProductForRangeOfTimes.xlsx?dl=0

 

[VBABEGINER]

This site is not opening at my end..security reason..

Try this: https://www.dropbox.com/s/8jrfm5gtwkbxgli/SumProductForRangeOfTimes.xlsx?dl=0

 

[MartyS]

This site is not opening at my end..security reason..

Use this:



Excel 2010

	A	B	C	D
3	Department	00:00	01:00	02:00
4	HR	1	0	0
5	IT	0	0	0
6	FINANCE	0	0	0

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B4 =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<>) <tbody> </tbody>

<tbody>
</tbody>

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

Excel 2010

	E	F	G
1	Dept		StartTime
2	HR		00:10
3	IT		13:30
4	FINANCE		13:30
5	FINANCE		13:30
6	HR		22:30
7	HR		22:30
8	FINANCE		13:30
9	HR		15:30
10	IT		13:30
11	IT		13:30
12	IT		22:30
13	FINANCE		13:30
14	HR		13:30
15	HR		22:30

<tbody>
</tbody>

Sheet2


​

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

After you've entered the formula into B4, copy and paste it to the relevant cells.

 

[VBABEGINER]

Absolutely Great..!! Hat's Off..

Use this:

 

[MartyS]

Absolutely Great..!! Hat's Off..

Wonderful!! Glad it worked.