Counting Unique Items in a column (Multiple Criteria)

[Amish Patel]

I have gone crazy solving this! Can anyone please help me?

I have two columns. The first contains list of Persons and the second
contains designs. I want to summarize the above data PERSONWISE
stating the total number of designs the person has. I want to avoid
pivot table since I would be further adding certains formulas in the
adjacent columns. I know there is a possibility of doing this by ARRAY
Formula or SUMPRODUCT but I am able to get a solution.

e.g for data below in sheet1
ColumnA ColumnB
Person Design
Tom Round
Tom Square
Dick Round
Harry Round
Tom Rectangle
Tom Round
Tom Square
Dick Round

The summary in sheet2 would contain
ColumnA ColumnB
Persons No.of Designs
Tom 3
Dick 1
Harry 1
Total Designs 3

Regards
Amish

 

[hgrove]

Amish Patel wrote...
...

I have two columns. The first contains list of Persons and the secon contains
designs. I want to summarize the above data PERSONWISE stating th total
number of designs the person has. . . . ...
e.g for data below in sheet1
Person Design
Tom Round
Tom Square
Dick Round
Harry Round
Tom Rectangle
Tom Round
Tom Square
Dick Round

I'll assume the rows containing the names and designs has the define
name TBL.

The summary in sheet2 would contain
Persons No.of Designs
Tom 3
Dick 1
Harry 1
Total Designs 3

If Tom were to appear in cell A2, then try these formulas.

A2:
=INDEX(TBL,1,1)

B2 [array formula]:
=SUM(1/MMULT(--(IF(INDEX(TBL,0,1)=A2,INDEX(TBL,0,2))
=TRANSPOSE(IF(INDEX(TBL,0,1)=A2,INDEX(TBL,0,2)))),ROW(TBL)^0))-1

A3 [array formula]:
=INDEX(TBL,MATCH(0,COUNTIF(A$2:A2,INDEX(TBL,0,1)),0),1)

B3 [array formula]:
=SUM(1/MMULT(--(IF(INDEX(TBL,0,1)=A3,INDEX(TBL,0,2))
=TRANSPOSE(IF(INDEX(TBL,0,1)=A3,INDEX(TBL,0,2)))),ROW(TBL)^0))-1

Select A3:B3 and fill down as far as needed. For the total of al
designs, use

=SUMPRODUCT(1/COUNTIF(INDEX(TBL,0,2),INDEX(TBL,0,2))

 

[Frank Kabel]

[...]

If Tom were to appear in cell A2, then try these formulas.

A2:
=INDEX(TBL,1,1)

B2 [array formula]:
=SUM(1/MMULT(--(IF(INDEX(TBL,0,1)=A2,INDEX(TBL,0,2))
=TRANSPOSE(IF(INDEX(TBL,0,1)=A2,INDEX(TBL,0,2)))),ROW(TBL)^0))-1

Hi Harlan
I think the following array formula would be much faster (at least on
my test data-> 8-10 times faster)

=COUNT(1/FREQUENCY(IF((INDEX(TBL,0,1)=A2)*(INDEX(TBL,0,2)<>""),MATCH(IN
DEX(TBL,0,2),INDEX(TBL,0,2),0)),ROW(INDIRECT("1:"&COUNTA(INDEX(TBL,0,2)
)))))

Drawback: Uses more nested function levels.

Regards
Frank

 

[Amish Patel]

Thanks to you all. Your solution worked perfectly. Can you please
explain me the logic or manner in which your magic formula works. I
fail to understand how it is working.

Regards
Amish