Count occurrences - Backwards compatible solutions

[morrowj04]

I require a spreedsheet that count how many at a particular time.

It works in the newest version of excel but i cant seem to get it to work on excel prior to the newest version.

Ive tried various ways and still cant get the formula to work correctly.

Test Rota.xlsx
	G	H
3
4	0300	4
5	0400	4
6	0500	5
7	0600	8
8	0700	10
9	0800	10
10	0900	7
11	1000	5
12	1100	5
13	1200	5
14	1300	5
15	1400	2
16	1500	2
17	1600	2
18	1700	2
19	1800	2
20	1900	2
21	2000	2
22	2100	0
Sheet1

Cell Formulas
Range	Formula
H4	H4	=SUMPRODUCT(COUNT(IF(LEFT($A$2:$A$27,2)=LEFT(G4,2),1,""))-COUNT(IF(RIGHT($A$2:$A$27,2)=LEFT(G4,2),1,"")))
H5:H22	H5	=SUMPRODUCT(COUNT(IF(LEFT($A$2:$A$27,2)=LEFT(G5,2),1,""))-COUNT(IF(LEFT(RIGHT($A$2:$A$27,4),2)=LEFT(G5,2),1,"")))+H4

 

[morrowj04]

I realise i never uploaded the full dataset

Test Rota.xlsx
	A	B	C	D	E
2
3	0300-0900
4	0300-0900		0300	4
5	0300-0900		0400	4
6	0730-2130		0500	5
7	0600-1400		0600	8
8	0500-1000		0700	10
9			0800	10
10	0600-1400		0900	7
11			1000	5
12			1100	5
13			1200	5
14	0300-1000		1300	5
15			1400	2
16			1500	2
17	0700-1200		1600	2
18	1200-2100		1700	2
19	0630-1430		1800	2
20			1900	2
21			2000	2
22			2100	0
23
24
Sheet1

Cell Formulas
Range	Formula
D4	D4	=SUMPRODUCT(COUNT(IF(LEFT($A$2:$A$27,2)=LEFT(C4,2),1,""))-COUNT(IF(RIGHT($A$2:$A$27,2)=LEFT(C4,2),1,"")))
D5:D22	D5	=SUMPRODUCT(COUNT(IF(LEFT($A$2:$A$27,2)=LEFT(C5,2),1,""))-COUNT(IF(LEFT(RIGHT($A$2:$A$27,4),2)=LEFT(C5,2),1,"")))+D4

 

[Scott Huish]

Why does 2100 have a 0 result? In your chart there is a 2100 and a 2130, so I would think it would be 2.

 

[morrowj04]

Why does 2100 have a 0 result? In your chart there is a 2100 and a 2130, so I would think it would be 2.

The reason this happens is because the first half adds on and the second half subtracts

Start Time	End Time
0300	2100

i havent yet but the overhanging If statement that corrects that behaviour