software

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 quot;2005, FEBquot; 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?

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

=A2amp;quot;/quot;amp;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,quot;=2005/Febquot;)

Hope this helps--
Kevin Backmannquot;Cashquot; wrote:

gt; I am trying to count the number of unique entries in a column of data but
gt; based on data in other columns. I need this to be automated versus filtering
gt; the data and cut/pasting, etc. For example, if I want to know how many unique
gt; entries in Column C are quot;2005, FEBquot; the answer I'm looking for is: 1
gt;
gt; COL A COL B COL C
gt; 2006 JAN 3
gt; 2006 FEB 4
gt; 2005 FEB 5
gt; 2005 FEB 5
gt; 2005 JAN 5
gt;
gt; Can one of your smart folks help me out?

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:A1 00=D1)*(B1:B100=E1)*C1:C100,(A1:A100=D1)*(B1:B100= E1)*C1:C100)gt;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-Proquot;Cashquot; wrote:

gt; I am trying to count the number of unique entries in a column of data but
gt; based on data in other columns. I need this to be automated versus filtering
gt; the data and cut/pasting, etc. For example, if I want to know how many unique
gt; entries in Column C are quot;2005, FEBquot; the answer I'm looking for is: 1
gt;
gt; COL A COL B COL C
gt; 2006 JAN 3
gt; 2006 FEB 4
gt; 2005 FEB 5
gt; 2005 FEB 5
gt; 2005 JAN 5
gt;
gt; Can one of your smart folks help me out?


Just use Data/Filter/adavnced filter amp; select Unique records only

---GJC--
gjcase
------------------------------------------------------------------------
gjcase's Profile: www.excelforum.com/member.php...oamp;userid=26061
View this thread: www.excelforum.com/showthread...hreadid=529579Assuming 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!

In article gt;,
Cash gt; wrote:

gt; I am trying to count the number of unique entries in a column of data but
gt; based on data in other columns. I need this to be automated versus filtering
gt; the data and cut/pasting, etc. For example, if I want to know how many unique
gt; entries in Column C are quot;2005, FEBquot; the answer I'm looking for is: 1
gt;
gt; COL A COL B COL C
gt; 2006 JAN 3
gt; 2006 FEB 4
gt; 2005 FEB 5
gt; 2005 FEB 5
gt; 2005 JAN 5
gt;
gt; Can one of your smart folks help me out?