Counting discrete references in pivot tables

I have a data table which looks something like this:

Ref Department Type
ENQ007740 Yellow Team AIR
ENQ007740 Yellow Team SEA
ENQ007745 Yellow Team SEA
ENQ007746 Red Team AIR
ENQ007748 Green Team AIR
ENQ007748 Green Team SEA

I want to count the number of enquiries by team.

So if I do this manually I would get:

Yellow 2
Red 1
Green 1

But if I use a pivot then I get:

Yellow 3
Red 1
Green 2

Because it can't recognise that some of the references are the same.

How can I get it to count discrete references only?

Thanks

if you search for count distinct you may get a clearer explaination, as that is how I first came accross the solution

but
if the data is in the range a2:b:7

=SUMPRODUCT(($A$2:$A$7<>"")/COUNTIF($A$2:$A$7,$A$2:$A$7&"")*($B$2:$B$7="yellow team")) etc

obviously you could replace yellow team with a cell containing the text and then copy it down for red and green teams

Regards

Dav

Thanks Dav,

Unfortunately I was being overly simplistic, so I don't think you solution will work.

Ref Department Type Status
ENQ007740 Yellow Team AIR Won
ENQ007740 Yellow Team SEA Won
ENQ007745 Yellow Team SEA Lost
ENQ007746 Red Team AIR Quoted
ENQ007748 Green Team AIR Won
ENQ007748 Green Team SEA Quoted

What I am trying to do is to sort as follows:

Won Lost Quoted
Yellow 1 1 0
Red 0 0 1
Green 1 0 1

As you can see where the reference is the same and the status is the same, I just want to be able to say that that enquiry was, in this case, won.

But as with the last reference, part was won and part quoted, so I want to have them split.

Thats why I was using a pivot table, but I can't get the pivot table to do this.

Any ideas?