I have a large amount of data- 2000 lines

stock code/ description/ depot sold to / qty sold

what I want to do is count the amount of stock codes in that sheet.

So product 'A' might appear up to 20 times at diferent depots and more than
once at a depot. But I just want to count A as 1 occurance.

When I pivot it counts the amount of times that product appears.

But I just want to know that e, I have 20 products occuring in the list..

Is there a pivot function to do this?

Copy the Stock Code header to an empty column
select the range of data
Use Data-Filter-Advanced Filter
use Copy to a new location & select the copied header
BE SURE to check Unique records only

You'll get a list of stock codes that appear in your data

"JONSPONGE" wrote:

I have a large amount of data- 2000 lines

stock code/ description/ depot sold to / qty sold

what I want to do is count the amount of stock codes in that sheet.

So product 'A' might appear up to 20 times at diferent depots and more than
once at a depot. But I just want to count A as 1 occurance.

When I pivot it counts the amount of times that product appears.

But I just want to know that e, I have 20 products occuring in the list..

Is there a pivot function to do this?

From a posting by Bob Phillips
=SUMPRODUCT((C2:C200<"")/COUNTIF(C2:C200,C2:C200&""))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JONSPONGE" wrote in message
...
I have a large amount of data- 2000 lines

stock code/ description/ depot sold to / qty sold

what I want to do is count the amount of stock codes in that sheet.

So product 'A' might appear up to 20 times at diferent depots and more
than
once at a depot. But I just want to count A as 1 occurance.

When I pivot it counts the amount of times that product appears.

But I just want to know that e, I have 20 products occuring in the list..

Is there a pivot function to do this?