Thank you for presenting a best case example of how to write a request!

With your posted data in A1:C7

Then
D1: Client Count
E1: =SUMPRODUCT((A2:A11<"")/COUNTIF(A2:A11,A2:A11&""))

D2: 94
This ARRAY FORMULA, is committed with CTRL+SHIFT+ENTER,
instead of just ENTER:
E2: =SUMPRODUCT(--(FREQUENCY(IF($B$2:$B$10=D2,$A$2:$A$10),$A$2:$A$10 )0))

D3: 99
E3: (copy the formula from E2 to E3)

D4: 3180
This is also an ARRAY FORMULA:
E4: =SUMPRODUCT(--(FREQUENCY(IF($C$2:$C$10=D4,$A$2:$A$10),$A$2:$A$10 )0))

D5: 3110
E5: (copy the formula from E4 to E5)

Is that something you can work with?
Post back if you have more questions.

Regards,

Ron
Microsoft MVP - Excel

"Andy" wrote in message
...
Greetings:

I export data from a database program into Excel. The challenge is that
program repeats data if there are more than one type of service or items
provided to the clients. Here is a sample of the data obtained:

Client ID Service Item
172 94 3180
469 99 3110
469 99 3150
469 99 3220
470 99 3110
470 79 3150

Control: Client 172 had service 94 and bought item 3180 (straight forward)
Client 469 had service 99 and bought items 3110, 3150 and 3220
Client 470 had service 99 and 79 and bought item 3110 and 3150.

I have been able to count individual clients using the
=SUM(IF(FREQUENCY(A1:A1,A1:A6)0,1)) but I am kinda stumped as to a
formula
that would give me individual results for services and products.

The results I would need for this data would be.

# of Clients = 3 (this part is fine with the sum/if/frequency formula)
# of 94 service = 1
# of 99 service = 2 (one for client 469 and one for client 470)
# of 3180 item = 1
# of 3110 item = 2 (one for client 469 and one for client 470)
# of 3150 item = 2 (one for client 469 and one for client 470)
# of 3220 item = 1

I suspect I will have to enter a unique formula for each item and service,
which is fine. I do hope I explained this appropriately.

Thank you for your time and expertise.

Andy