Count without duplicates

[shakaz]

Let's say I have three columns of data.

In Column A, is name: Bob, Jon, Mary, Bob etc
In Column B, is Billed or Un-billed: Billed, Bench, unbilled etc.

In column A, there will be instances where a name might appear more than once. I want count How many are Billed in Column B but for 1 instance not for duplicates. Is there a way to do this?

Bob Billed
Jon Bench
Bob Bench
Bob Billed
Mary Billed

Here Billed count is 2 (Bob is repeated). Can you help????????

 

[Roger Govier]

Hi

One way would be to use a helper column.
In cell C1 enter
=IF(COUNTIF($A$1:A1,A1)>1,0,1)
and copy down

Then in cell D1 enter
=SUMIF(B1:B5,"Billed",C1:C5)

You can hide column C if you wish.

 

[JackDanIce]

Select all your data, use ADVANCED FILTER for unique values only and then you can use the COUNTIF function to count the number of unique items in column B