Meeting Set Criteria and counting if within a date range ?

[Roy JWR]

Colum A

Ipswich
Manchester
Bolton
Aberdeen

Colum B

11/2/21
8/2/21
7/2/21
15/2/21

I am looking for a formula that will:-
a) Look for a Defined location in the list on Colum A
b) IF a) is met then count if between two date Colum B

eg: Look For "Ipswich" IF found within the range, was it serviced between the 1/2/21 and 7/2/21 then count.
Ipswich may appear 50 times within Colum A and may have been serviced 20 times within the date range.

Thank you

 

[mart37]

Have you tried COUNTIFS?

 

[Roy JWR]

Have you tried COUNTIFS?

Yeh, but it keep coming up #value

 

[mart37]

Map3
	A	B	C	D	E
1	city	date		city	ipswich
2	Ipswich	11-2-2021		startdate	1-2-2021
3	Manchester	8-2-2021		enddate	7-2-2021
4	Bolton	7-2-2021		count	2
5	Aberdeen	15-2-2021
6	Ipswich	1-2-2021
7	Manchester	2-2-2021
8	Bolton	3-2-2021
9	Aberdeen	4-2-2021
10	Ipswich	5-2-2021
11	Manchester	6-2-2021
12	Bolton	7-2-2021
13	Aberdeen	8-2-2021
14	Ipswich	9-2-2021
15	Manchester	10-2-2021
16	Bolton	11-2-2021
17	Aberdeen	12-2-2021
Blad1

Cell Formulas
Range	Formula
E4	E4	=COUNTIFS(A2:A17,E1,B2:B17,">="&E2,B2:B17,"<="&E3)

 

[Roy JWR]

Thank you

 

[Roy JWR]

Working, THANK YOU