Count multiple criteria from data table and insert frequency in seperate date and time table

[Chilly2011]

Hello, I've researched this problem through many sources including this forum (which offer solutions to similar problems) however I've come to a dead end due to my limited excel knowledge.

I have the below data table example:

Excel 2013 32 bit

	A​	B​	C​	D​
1​	YYYY-MM-DD​	hh:mm:ss​	Dr​	Speed​
2​	9/04/2016​	16:11:51​	W0​	86​
3​	9/04/2016​	16:14:44​	W0​	92​
4​	9/04/2016​	16:14:46​	W0​	97​
5​	9/04/2016​	16:14:49​	W0​	92​
6​	9/04/2016​	16:15:37​	E1​	96​
7​	9/04/2016​	16:15:50​	E1​	97​
8​	9/04/2016​	16:17:24​	W0​	91​
9​	9/04/2016​	16:18:01​	W0​	105​
10​	9/04/2016​	16:18:58​	W0​	101​
11​	9/04/2016​	16:19:20​	W0​	88​
12​	9/04/2016​	16:20:41​	W0​	88​
13​	9/04/2016​	16:22:13​	E1​	94​
14​	9/04/2016​	16:23:09​	W0​	110​

<tbody>
</tbody>

Sheet: 20209

<tbody>
</tbody>

I'm wanting to count the frequency of each occurrence which match four (4) criteria: 1. Date; 2. Time falls within the relevant hourly time bins; 3. Direction of travel "W0" and 4. The speed from column D is > 109 kmp/h. I created a second table in order to record the frequency of each occurrence from which I was hoping to conditionally format or create scatter charts from to identify trends:

	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​	O​	P​	Q​	R​	S​	T​	U​	V​	W​	X​	Y​	Z​	AA​
2​	Speed	109​
3​	Direction	W0​
4​			00:00:00​	01:00:00​	02:00:00​	03:00:00​	04:00:00​	05:00:00​	06:00:00​	07:00:00​	08:00:00​	09:00:00​	10:00:00​	11:00:00​	12:00:00​	13:00:00​	14:00:00​	15:00:00​	16:00:00​	17:00:00​	18:00:00​	19:00:00​	20:00:00​	21:00:00​	22:00:00​	23:00:00​
5​			01:00:00​	02:00:00​	03:00:00​	04:00:00​	05:00:00​	06:00:00​	07:00:00​	08:00:00​	09:00:00​	10:00:00​	11:00:00​	12:00:00​	13:00:00​	14:00:00​	15:00:00​	16:00:00​	17:00:00​	18:00:00​	19:00:00​	20:00:00​	21:00:00​	22:00:00​	23:00:00​	00:00:00​
6​	Fri​	8/04/2016​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​
7​	Sat​	9/04/2016​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​
8​	Sun​	10/04/2016​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​	0​

<tbody>
</tbody>

Sheet: Time Chart

<tbody>
</tbody>

I tried using a series of Countif's however this returned a series of "0's. I only want 0's to display if there is no value for the relevant hourly time bin.

	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​	O​	P​	Q​	R​	S​	T​	U​	V​	W​	X​	Y​	Z​	AA​
2​	Speed	109​
3​	Direction	W0​
4​			00:00:00​	01:00:00​	02:00:00​	03:00:00​	04:00:00​	05:00:00​	06:00:00​	07:00:00​	08:00:00​	09:00:00​	10:00:00​	11:00:00​	12:00:00​	13:00:00​	14:00:00​	15:00:00​	16:00:00​	17:00:00​	18:00:00​	19:00:00​	20:00:00​	21:00:00​	22:00:00​	23:00:00​
5​			01:00:00​	02:00:00​	03:00:00​	04:00:00​	05:00:00​	06:00:00​	07:00:00​	08:00:00​	09:00:00​	10:00:00​	11:00:00​	12:00:00​	13:00:00​	14:00:00​	15:00:00​	16:00:00​	17:00:00​	18:00:00​	19:00:00​	20:00:00​	21:00:00​	22:00:00​	23:00:00​	00:00:00​
6​	Fri​	8/04/2016​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=D$4,Time20209,"'Time Chart'!"<d$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</d$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=E$4,Time20209,"'Time Chart'!"<e$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</e$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=F$4,Time20209,"'Time Chart'!"<f$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</f$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=G$4,Time20209,"'Time Chart'!"<g$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</g$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=H$4,Time20209,"'Time Chart'!"<h$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</h$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=I$4,Time20209,"'Time Chart'!"<i$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</i$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=J$4,Time20209,"'Time Chart'!"<j$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</j$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=K$4,Time20209,"'Time Chart'!"<k$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</k$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=L$4,Time20209,"'Time Chart'!"<l$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</l$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=M$4,Time20209,"'Time Chart'!"<m$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</m$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=N$4,Time20209,"'Time Chart'!"<n$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</n$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=O$4,Time20209,"'Time Chart'!"<o$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</o$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=P$4,Time20209,"'Time Chart'!" )</p$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=Q$4,Time20209,"'Time Chart'!"<q$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</q$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=R$4,Time20209,"'Time Chart'!"<r$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</r$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=S$4,Time20209,"'Time Chart'!"<s$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</s$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=T$4,Time20209,"'Time Chart'!"<t$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</t$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=U$4,Time20209,"'Time Chart'!"<u$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</u$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=V$4,Time20209,"'Time Chart'!"<v$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</v$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=W$4,Time20209,"'Time Chart'!"<w$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</w$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=X$4,Time20209,"'Time Chart'!"<x$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</x$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=Y$4,Time20209,"'Time Chart'!"<y$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</y$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=Z$4,Time20209,"'Time Chart'!"<z$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</z$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C6,Time20209,"'Time Chart'!">=AA$4,Time20209,"'Time Chart'!"<aa$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</aa$5,direction20209,"time>​
7​	Sat​	9/04/2016​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=D$4,Time20209,"'Time Chart'!"<d$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</d$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=E$4,Time20209,"'Time Chart'!"<e$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</e$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=F$4,Time20209,"'Time Chart'!"<f$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</f$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=G$4,Time20209,"'Time Chart'!"<g$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</g$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=H$4,Time20209,"'Time Chart'!"<h$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</h$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=I$4,Time20209,"'Time Chart'!"<i$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</i$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=J$4,Time20209,"'Time Chart'!"<j$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</j$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=K$4,Time20209,"'Time Chart'!"<k$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</k$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=L$4,Time20209,"'Time Chart'!"<l$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</l$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=M$4,Time20209,"'Time Chart'!"<m$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</m$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=N$4,Time20209,"'Time Chart'!"<n$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</n$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=O$4,Time20209,"'Time Chart'!"<o$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</o$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=P$4,Time20209,"'Time Chart'!" )</p$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=Q$4,Time20209,"'Time Chart'!"<q$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</q$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=R$4,Time20209,"'Time Chart'!"<r$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</r$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=S$4,Time20209,"'Time Chart'!"<s$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</s$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=T$4,Time20209,"'Time Chart'!"<t$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</t$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=U$4,Time20209,"'Time Chart'!"<u$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</u$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=V$4,Time20209,"'Time Chart'!"<v$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</v$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=W$4,Time20209,"'Time Chart'!"<w$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</w$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=X$4,Time20209,"'Time Chart'!"<x$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</x$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=Y$4,Time20209,"'Time Chart'!"<y$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</y$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=Z$4,Time20209,"'Time Chart'!"<z$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</z$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C7,Time20209,"'Time Chart'!">=AA$4,Time20209,"'Time Chart'!"<aa$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</aa$5,direction20209,"time>​
8​	Sun​	10/04/2016​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=D$4,Time20209,"'Time Chart'!"<d$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</d$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=E$4,Time20209,"'Time Chart'!"<e$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</e$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=F$4,Time20209,"'Time Chart'!"<f$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</f$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=G$4,Time20209,"'Time Chart'!"<g$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</g$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=H$4,Time20209,"'Time Chart'!"<h$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</h$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=I$4,Time20209,"'Time Chart'!"<i$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</i$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=J$4,Time20209,"'Time Chart'!"<j$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</j$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=K$4,Time20209,"'Time Chart'!"<k$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</k$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=L$4,Time20209,"'Time Chart'!"<l$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</l$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=M$4,Time20209,"'Time Chart'!"<m$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</m$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=N$4,Time20209,"'Time Chart'!"<n$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</n$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=O$4,Time20209,"'Time Chart'!"<o$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</o$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=P$4,Time20209,"'Time Chart'!" )</p$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=Q$4,Time20209,"'Time Chart'!"<q$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</q$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=R$4,Time20209,"'Time Chart'!"<r$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</r$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=S$4,Time20209,"'Time Chart'!"<s$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</s$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=T$4,Time20209,"'Time Chart'!"<t$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</t$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=U$4,Time20209,"'Time Chart'!"<u$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</u$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=V$4,Time20209,"'Time Chart'!"<v$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</v$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=W$4,Time20209,"'Time Chart'!"<w$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</w$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=X$4,Time20209,"'Time Chart'!"<x$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</x$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=Y$4,Time20209,"'Time Chart'!"<y$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</y$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=Z$4,Time20209,"'Time Chart'!"<z$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</z$5,direction20209,"time>​	=COUNTIFS(Date20209,"Time Chart'!"=$C8,Time20209,"'Time Chart'!">=AA$4,Time20209,"'Time Chart'!"<aa$5,direction20209,"time chart'!"="$C$3,Speed20209,"'Time">)</aa$5,direction20209,"time>​

<tbody>
</tbody>

Sheet: Time Chart

<tbody>
</tbody>

Am I correct in thinking I need to include a "look-up function" such as index and match to make this work?


Thanks

 

[jorismoerings]

Hi,

I don't think you need a "lookup function", COUNTIF is from my point of view perfectly capable of doing what you want.
The formula's you're using aren't correct excel formula's

i think this what you're after but be aware; your example table has entry which aren't within the limits so any formula will be returning 0 as a result. If you want to check if this works change one of the enries to match your criteria.

Excel 2013 32 bit
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2	Speed	109
3	Direction	W0
4			00:00:00	01:00:00	02:00:00	03:00:00	04:00:00	05:00:00	06:00:00	07:00:00	08:00:00	09:00:00	10:00:00	11:00:00	12:00:00	13:00:00	14:00:00	15:00:00	16:00:00	17:00:00	18:00:00	19:00:00	20:00:00	21:00:00	22:00:00	23:00:00
5			01:00:00	02:00:00	03:00:00	04:00:00	05:00:00	06:00:00	07:00:00	08:00:00	09:00:00	10:00:00	11:00:00	12:00:00	13:00:00	14:00:00	15:00:00	16:00:00	17:00:00	18:00:00	19:00:00	20:00:00	21:00:00	22:00:00	23:00:00	00:00:00
6	Fri	8-4-2016	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7	Sat	9-4-2016	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
8	Sun	10-4-2016	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Time Chart

Cell Formulas
Range	Formula
C6	=COUNTIFS('20209'!$D$2:$D$14,">="&$B$2,'20209'!$C$2:$C$14,$B$3,'20209'!$A$2:$A$14,$B6,'20209'!$B$2:$B$14,"<="&C$5)
D6	=COUNTIFS('20209'!$D$2:$D$14,">="&$B$2,'20209'!$C$2:$C$14,$B$3,'20209'!$A$2:$A$14,$B6,'20209'!$B$2:$B$14,">="&D$4,'20209'!$B$2:$B$14,"<="&D$5)
Z6	=COUNTIFS('20209'!$D$2:$D$14,">="&$B$2,'20209'!$C$2:$C$14,$B$3,'20209'!$A$2:$A$14,$B6,'20209'!$B$2:$B$14,">="&Z$4)

be aware the formula for the first Bin and the last bin are different from the one in between! Copy D6 to the range E6:Y6

Hope this helps.

 

[Chilly2011]

Thank you very much for taking the time to respond to me. I'll follow your advice and see if I have any luck. Thanks again.

 

[Chilly2011]

Thanks suggestion worked very well thanks Joris, I'm assuming that the order of the COUNTIF's in this case is very important?

 

[jorismoerings]

Hi,

No, You can either change the order or add extra criteria to your own need.
I just entered the criteria in the most logical way (at least for me!) to understand and keep track on what the formula is doing.

 

[Chilly2011]

Okay thanks Joris, if I could buy you a beer I would.

Regards
Jade

 

[Chilly2011]

Joris I'm wondering if you could help me further with another formula again relating to the same scenario above?

I have created drop down box's that list: Speed (B2), Direction (B3) and now Vehicle Type which would be in cell (B4). These change the values in my table which in turn changes the conditional formatting colours to identify trends.

My goal is to be able to count both directions (W0 and E1) together when the word "Both" is selected from the drop down box in cell B3. (The drop down box choices would be "Both", W0 and E1). Also I want to be able to count all vehicle types when the word "All" is selected from the drop down box in cell B4. (The drop down box choices would be "All", "MC, SV, SVT, TB2, TB3, ART2 etc). I haven't included the formulas for the vehicle types yet since the last time I spoke with you.

Regards

Jade