[Neall]

I have a list of customers over the past 4 years, they are differentiated by
customer numbers.

What I would like to do is in one cell per year give me a unique customer
count for that year.

Then in another set of cells per year I would like to get a unique sum of
all purchased parts.

Customer number is A1$1
End date is G1$1
Pricing is H1$1


Any suggestions


--
Neall

[Bernard Liengme[_3_]]

counting unique numbers
=SUMPRODUCT(--(A1:A100<""),--(1/COUNTIF(A1:A100,A1:A10&"")),A1:A100)

Is unclear: Then in another set of cells per year I would like to get a
unique sum of all purchased parts.

best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Neall" wrote in message
...
I have a list of customers over the past 4 years, they are differentiated
by
customer numbers.

What I would like to do is in one cell per year give me a unique customer
count for that year.

Then in another set of cells per year I would like to get a unique sum of
all purchased parts.

Customer number is A1$1
End date is G1$1
Pricing is H1$1


Any suggestions


--
Neall

[Shane Devenshire[_2_]]

Hi,

To count the number of unique customer numbers per year enter the year in J1
and down, then enter the following array formula:

=COUNT(1/FREQUENCY(IF((YEAR(G$2:G$21)=J1),A$2:A$21),A$2:A$2 1))

Press Shift+Ctrl+Enter to enter it to make it an array.

The sum by year would be

=SUMPRODUCT(--(YEAR($G$2:$G$21)=J1),$H$2:$H$21)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Neall" wrote:

I have a list of customers over the past 4 years, they are differentiated by
customer numbers.

What I would like to do is in one cell per year give me a unique customer
count for that year.

Then in another set of cells per year I would like to get a unique sum of
all purchased parts.

Customer number is A1$1
End date is G1$1
Pricing is H1$1


Any suggestions


--
Neall