Unique cities based on several criterias

[baleshst]

Location	Company	TYPE	Operational from	Zone	State	City
Kolkata City Center	FINOX	Leased	18-Jun-04	East	West Bengal	Kolkata
Kolkata Swabhumi	FINOX	Leased	25-Jun-04	East	West Bengal	Kolkata
Delhi Janak Place	Jayam	Leased	8-Aug-14	North	Delhi	Delhi
Mumbai Malad	Dame	Leased	1-Apr-10	West	Maharashtra	Mumbai
Nashik	Dame	Leased	1-Apr-10	West	Maharashtra	Nasik
Mumbai Nariman Point	FINOX	Leased	12-Nov-04	West	Maharashtra	Mumbai
Goa GMC	FINOX	Leased	13-Nov-04	West	Goa	Goa
As on	31-07-14
count of Unique Cities for leased	FINOX	Dame	Jayam	Total
East
West
North
South

<tbody>
</tbody>

Please provide solution for the above table.

Count of Unique city names should populate in the blue sections based on the following criteria

1)As on Date (which is dependent on the Operational from column)
2)company name
3)zone
4)Type

 

[Michael M]

why not use a Pivot table ??

 

[baleshst]

why not use a Pivot table ??

cannot use pivot, need a formula to insert.

 

[Mike Szczesny]

Can you something like this?

Excel 2012
	A	B	C	D
1	Location	Company	Date	Zone
2	a	1	06/18/14	North
3	b	1	06/25/14	South
4	c	2	08/15/14	East
5	d	3	07/13/14	West
6	e	1	09/30/14	North
7	f	2	09/20/11	West
8	g	3	08/12/12	South
9	h	1	01/15/14	West
10
11	As on	7/14/2014
12
13		1	2	3
14	North	1	0	0
15	South	1	0	1
16	East	0	0	0
17	West	1	1	1
Sheet1

Cell Formulas
Range	Formula
B14	=SUMPRODUCT(($D$2:$D$9=$A14)*($B$2:$B$9=B$13)*($C$2:$C$9<=$B$11))
C14	=SUMPRODUCT(($D$2:$D$9=$A14)*($B$2:$B$9=C$13)*($C$2:$C$9<=$B$11))
D14	=SUMPRODUCT(($D$2:$D$9=$A14)*($B$2:$B$9=D$13)*($C$2:$C$9<=$B$11))

 

[Aladin Akyurek]

How does 31-07-14 relate to the values of the "Operational from" field?