Counting unique values

[blswes]

Trying to count how many customers there were in 2006 only vs. 2007 only vs.
2006 & 2007.

Data's set up with Customer ID and Year columns, so if a customer is part of
both 2006 and 2007, then there would be two separate rows (one for each year).

Any suggestions?

 

[Roger Govier]

Hi

=SUMPRODUCT(--(A1:A1000),--(B1:B1000) ) for 2006
=SUMPRODUCT(--(A1:A1000),--(C1:C1000)) for 2007
=SUMPRODUCT(--(A1:A1000),--(B1:B1000),--(C1:C1000)) for both years

 

[walpolem]

Trying to count how many customers there were in 2006 only vs. 2007 only vs.
2006 & 2007.

Data's set up with Customer ID and Year columns, so if a customer is partof
both 2006 and 2007, then there would be two separate rows (one for each year).

Any suggestions?

Use the Count function

=COUNT(C1:C2) - based of a sort of 2006 C1:C2 = Cell Range
=COUNT(C1:C2) - based of a sort of 2007 C1:C2 = Cell Range
=COUNT(C1:C2) - based of a sort of 2006 & 2007 C1:C2 = Cell Range

 

[blswes]

I think I may have described the situation too vaguely.

In column A (let's say), I have Customer IDs. And in column B, I have Year
(either 2006 or 2007).

Therefore, a Customer ID that's in both 2006 and 2007 ends up with an entry
in two rows (one for 2006 and one for 2007).

How do I count how many Customer IDs show up for only 2006 vs. only 2007 vs.
both 2006 and 2007?

 

[Roger Govier]

Hi

Since the Year is held in column B, (I had thought one year was in B and 1
in C), then use
=SUMPRODUCT(--(A1:A1000),--(B1:B1000=2006) ) for 2006
=SUMPRODUCT(--(A1:A1000),--(B1:B1000)=2007 ) for 2007
=SUMPRODUCT(--(A1:A1000)*(B1:B1000=2006)+(B1:B1000=2007)) for 2006&2007