Count unique entries

[Guest]

I am trying to count the number of unique entries in a column of data but
based on data in other columns. I need this to be automated versus filtering
the data and cut/pasting, etc. For example, if I want to know how many unique
entries in Column C are "2005, FEB" the answer I'm looking for is: 1

COL A COL B COL C
2006 JAN 3
2006 FEB 4
2005 FEB 5
2005 FEB 5
2005 JAN 5

Can one of your smart folks help me out?

 

[Guest]

I created a fourth column to concatenate the year and the month together and
then used a CountIf to get the value I wanted.

The formula I used to concatenate the dates is

=A2&"/"&B2

Which is copied down the column to capture all your data.

The following COUNTIF function counts the number of 2005/Feb entries:

=COUNTIF(D15,"=2005/Feb")

Hope this helps

 

[Guest]

See if this works for you....

Example with data in A1:C100
Col_A contains years
Col_B contains months
Col_C contains numeric values

D1: 2005
E1: JAN
F1:
=SUM(((A1:A101=D1)*(B1:B101=E1))*(FREQUENCY((A1:A100=D1)*(B1:B100=E1)*C1:C100,(A1:A100=D1)*(B1:B100=E1)*C1:C100)>0))

That array formula returns the count of Unique combination from Col_C where
Col_A=2005 and Col_B=JAN. In this formula if 3 items contain
2005,Jan,6....they count as ONE unique combination

Note 1: To commit array formulas, hold down [Ctrl] and [Shift] when you
press [Enter].

Note 2: some of the references point down to row 101 (one row beyond the
data range). That is not an error.

Is that what you're looking for?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[gjcase]

Just use Data/Filter/adavnced filter & select Unique records only

---GJ

 

[Domenic]

Assuming that A2:C6 contains the data, let E2 contain the year, and F2
the month, then try the following formula, which needs to be confirmed
with CONTROL+SHIFT+ENTER, not just ENTER...

=COUNT(1/FREQUENCY(IF(A2:A6=E2,IF(B2:B6=F2,C2:C6)),IF(A2:A6=E2,IF(B2:B6=F
2,C2:C6))))

Hope this helps!