Hi, I have a spreadsheet where each line is a delivery of a certain product
(a drop). There can be several drops to a trip. I need the count of the trips
and trip numbers are not unique. To create a unique identifier, I have
combined the supply site with the trip number. Trip numbers at any one site
are unique. There are thousands of them. My problem is that I cannot get
Excel to count the number of trips correctly.

As an example I have the following trips numbers from site 3969:
5001, 5002, 5003 which when combined with site number gives
39695001,39695002, 39695003. Because each trip can have multiple drops these
unique trip numbers will repeat for the number of drops involved in the trip.
This is what happens which is expected:

39695001
39695001
39695002
39695002
39695002

The answer I am looking for here is 2 trips numbered 39695001 and 39695002.
The answer Excel gives me is 5 - the count of the drops which is not what I
want. I've tried this in pivot tables and various other means without
success. What should I do?

Assuming pivoted source data as posted within A1:A10,
then something like this in a cell outside of the pivot:
=SUMPRODUCT(--(A1:A10<""),1/COUNTIF(A1:A10,A1:A10&""))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
---
"markmcd" wrote:
Hi, I have a spreadsheet where each line is a delivery of a certain product
(a drop). There can be several drops to a trip. I need the count of the trips
and trip numbers are not unique. To create a unique identifier, I have
combined the supply site with the trip number. Trip numbers at any one site
are unique. There are thousands of them. My problem is that I cannot get
Excel to count the number of trips correctly.

As an example I have the following trips numbers from site 3969:
5001, 5002, 5003 which when combined with site number gives
39695001,39695002, 39695003. Because each trip can have multiple drops these
unique trip numbers will repeat for the number of drops involved in the trip.
This is what happens which is expected:

39695001
39695001
39695002
39695002
39695002

The answer I am looking for here is 2 trips numbered 39695001 and 39695002.
The answer Excel gives me is 5 - the count of the drops which is not what I
want. I've tried this in pivot tables and various other means without
success. What should I do?