We have a multi-row, multi-column spreadsheet. For each unique value in
Column "A", we want to count the unique values in Column "B".

We tried a pivot table, but get a total count, instead of the unique values.

We then tried a two-step process. 1) we created the unique list in Column A
by Pivot Table or by Autofiter. 2) The formula below (as an array), but
again, it gave a total count, instead of the unique count.

=COUNT(IF('2008-2009 (till Nov) TR'!E2:E5033=Sheet2!H2,COUNT('2008-2009
(till Nov) TR'!D2:D5033),"")).

We also tried the subtotal functional, but it required many steps and many
sheets, because we will repeats using many different conditions.

With data in below format from A1 to B6 with out blanks...in between

Col A Col B
apple a
apple a
apple a
orange a
orange b
orange c

the below array formula will return the number of unique entries of 'Apple'
in colB

=SUM(N(FREQUENCY(IF((A1:A6="apple"),
MATCH(B1:B6,B1:B6,)),MATCH(B1:B6,B1:B6,))0))

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"PAL" wrote:

We have a multi-row, multi-column spreadsheet. For each unique value in
Column "A", we want to count the unique values in Column "B".

We tried a pivot table, but get a total count, instead of the unique values.

We then tried a two-step process. 1) we created the unique list in Column A
by Pivot Table or by Autofiter. 2) The formula below (as an array), but
again, it gave a total count, instead of the unique count.

=COUNT(IF('2008-2009 (till Nov) TR'!E2:E5033=Sheet2!H2,COUNT('2008-2009
(till Nov) TR'!D2:D5033),"")).

We also tried the subtotal functional, but it required many steps and many
sheets, because we will repeats using many different conditions.

Data in the range A1:B20.

Assume you have a list of the unique entries from column A in the range D1:D5.

Enter this array formula** in E1 and copy down to E5:

=SUM(IF(FREQUENCY(IF(A$1:A$20=D1,MATCH(B$1:B$20,B$ 1:B$20,0)),ROW(B$1:B$20)-ROW(B1)+1),1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Assumes no empty cells within thrange in column A.

--
Biff
Microsoft Excel MVP


"PAL" wrote:

We have a multi-row, multi-column spreadsheet. For each unique value in
Column "A", we want to count the unique values in Column "B".

We tried a pivot table, but get a total count, instead of the unique values.

We then tried a two-step process. 1) we created the unique list in Column A
by Pivot Table or by Autofiter. 2) The formula below (as an array), but
again, it gave a total count, instead of the unique count.

=COUNT(IF('2008-2009 (till Nov) TR'!E2:E5033=Sheet2!H2,COUNT('2008-2009
(till Nov) TR'!D2:D5033),"")).

We also tried the subtotal functional, but it required many steps and many
sheets, because we will repeats using many different conditions.

Excel 2007 PivotTable
Three ways.
No formulas, no code.
http://www.mediafire.com/file/ywzmi2...11_05_09a.xlsx