Unique count is criteria is met
 

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

Unique count is criteria is met
 

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

Unique count is criteria is met
 

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