Tough Sumproduct Question

[Rich]

I have a tough question for the group. I'm not sure how
to code a function to handle the following. Here is
sample data:
ColA ColB

23A SMITH
23A SMITH
23A SMITH
10B BROWN
10B BROWN
10B BROWN
11C SMITH
11C SMITH
3F KENT
4D PARKER
4D PARKER
4D PARKER
4D PARKER
20F SMITH
20F SMITH
15C KENT
15C KENT
15C KENT


I would like to use a function that counts how many
instances a name in column B is found in conjuction with
the number of instances it is found in column A. For
example, for Smith, the result would be 3 because it shows
up 3 times (i.e. once with 23A-Smith, once with 11C-Smith,
and once with 20F-Smith). Brown would be 1, Kent would be
2, and Parker would return 1. Please help.
Sincerely,
Rich K.

 

[Aladin Akyurek]

Let A219 house the sample you provided and D2 "SMITH", a criterion name.

Array-enter, that is, confirm the formula using control+shift+enter at the
same time, not just enter...

=COUNTDIFF(IF($B$2:$B$19=D2,$A$2:$A$19))-1

COUNTDIFF is a function from the morefunc.xll add-in that you can download
from:

http://longre.free.fr/english/index.html

 

[Anon]

I have a tough question for the group. I'm not sure how
to code a function to handle the following. Here is
sample data:
ColA ColB

23A SMITH
23A SMITH
23A SMITH
10B BROWN
10B BROWN
10B BROWN
11C SMITH
11C SMITH
3F KENT
4D PARKER
4D PARKER
4D PARKER
4D PARKER
20F SMITH
20F SMITH
15C KENT
15C KENT
15C KENT


I would like to use a function that counts how many
instances a name in column B is found in conjuction with
the number of instances it is found in column A. For
example, for Smith, the result would be 3 because it shows
up 3 times (i.e. once with 23A-Smith, once with 11C-Smith,
and once with 20F-Smith). Brown would be 1, Kent would be
2, and Parker would return 1. Please help.
Sincerely,
Rich K.

This is not a sumproduct question.
You are just talking about joining text in two columns (A1&B1, etc) and then
counting unique entries.
How to count unique entries in a list is described here:
http://www.cpearson.com/excel/duplicat.htm#CountingUnique

 

[Harlan Grove]

...
This is not a sumproduct question.
You are just talking about joining text in two columns (A1&B1, etc) and then
counting unique entries.
How to count unique entries in a list is described here:
http://www.cpearson.com/excel/duplicat.htm#CountingUnique

Picky: the first formula in the section of this site to which you've given the
url states that it won't include blank cells. In the context of needing to make
a 2-column range into a 1-column logical list, the simple approach would be
A1:A18&B1:B18 or (cleverer) A1:A18&CHAR(127)&B1:B18. However, simplistic
construction of a Range1 in this way means that an entirely blank row would be
included as a distinct entry. Neither of the other two formulas in that section
would work at all for the OP.

If the OP wants to exclude possible entirely blank rows, he could use the array
formula

=SUMPRODUCT((1-ISBLANK(A1:A18)*ISBLANK(B1:B18))/MMULT(--(A1:A18&CHAR(127)&B1:B18=TRANSPOSE(A1:A18&CHAR(127)&B1:B18)),ROW(A1:B18)^0))

Note: this *includes* cells evaluating to zero-length strings ('quasiblank'
cells), which Chip's first formula doesn't. To exclude quasiblank as well as
blank cells, change this formula to

=SUMPRODUCT((1-(A1:A18="")*(B1:B18=""))/MMULT(--(A1:A18&CHAR(127)&B1:B18=TRANSPOSE(A1:A18&CHAR(127)&B1:B18)),ROW(A1:B18)^0))

Less picky: there's a subtle potential bug in Chip's first formula. It involves
the presence of wildcard characters in entries in the list. In the OP's data, if
row 2 column 1 entry were *A rather than 23A, Chip's formula would give 7 rather
than 8 because MATCH("*A",{"23A";"*A"}},0) returns 1 rather than 2.