Pivot Table help with counts

[CraigG]

I have data:

COMPANY 1:	COMPANY 2:	STATUS:	PROVIDES:
Abc Limited	123 and Co	Live	Product1
Abc Limited	123 and Co	Live	Product2
Abc Limited	jk34jhknfdskjfns and Co	Live	Product3
Xyz Limited	sghjdgshjab and Co	Live	Product4

I need the pivot table to look like:

FILTER:
STATUS = Live

COMPANY1	Count of COMPANY2	Count of STATUS:
Abc Limited	2	3
Xyz Limited	1	1

I can create the pivot table counting number of live statuses against COMPANY1 no problem, what I'm struggling with is doing the Count of COMPANY2. If I add COMPANY2 to values it won't count unique values it just counts the total number of occurrences, eg in the above data it would count 3 for Abc Limited. I can't add COMPANY2 to Columns as I have too much data and would end up with 100s of columns.

Any help greatly appreciated on how I can do this.

Thanks

 

[RoryA]

If you add the data to the data model (won't work in 2007 though), you will be able to use the Distinct Count option.