Use Pivot Table to count unique occurences of value

Hi guys.
I need some help summarising a large amount of data A small extract of which is attached.
The source data runs to some 2000 lines.
My issue is I have several sites and a number of devices at each site. Because the devices have multiple slots I have multiple instances of the same device on each site.
I want to run a pivot table that summarises.
So in the the example below I have 28 lines of data but they refer to only 4 devices, 2 at site BEL and 2 at site PDS.
I cannot get my pivot table to show this as it keeps counting each of the lines.
I know it's down to my lack of understanding with excel, but I'm getting frustrated and need to get this task down.

Please help

BZ

Site DEVICE_HOST_NAME VTP Domain
BEL BEL1015wana-d1 NETAGG
BEL BEL1015wana-d1 NETAGG
BEL BEL1015wana-d1 NETAGG
BEL BEL1015wana-d1 NETAGG
BEL BEL1015wana-d1 NETAGG
BEL BEL1015wana-d1 NETAGG
BEL BEL1015wana-d1 NETAGG
BEL BEL1015wana-d1 NETAGG
BEL BEL6007wana-d2 NETAGG
BEL BEL6007wana-d2 NETAGG
BEL BEL6007wana-d2 NETAGG
BEL BEL6007wana-d2 NETAGG
BEL BEL6007wana-d2 NETAGG
BEL BEL6007wana-d2 NETAGG
BEL BEL6007wana-d2 NETAGG
BEL BEL6007wana-d2 NETAGG
PDS PDS8025wana-d1 NETAGG
PDS PDS8025wana-d1 NETAGG
PDS PDS8025wana-d1 NETAGG
PDS PDS8025wana-d1 NETAGG
PDS PDS8025wana-d1 NETAGG
PDS PDS8025wana-d1 NETAGG
PDS PDS8027wana-d2 NETAGG
PDS PDS8027wana-d2 NETAGG
PDS PDS8027wana-d2 NETAGG
PDS PDS8027wana-d2 NETAGG
PDS PDS8027wana-d2 NETAGG
PDS PDS8027wana-d2 NETAGG

Re: Use Pivot Table to count unique occurences of value

Hi Thanks for your reply but I can't get it to work. Is there a particular way I should write the formula.
Is "#This Row" a command or do I write that freehand as that's where it seems to break down

Re: Use Pivot Table to count unique occurences of value

Thanks once again.
That now works a treat.

BZ