SUMPRODUCT to count items with duplicates where another column contains two defined items

[PCLIVE]

Ok, I think this is a SUMPRODUCT delima.

A B C D
z 1
q 1
z 5
w 0
z 2
q 2

I'd like to get a count of duplicate values in column A that have both 1 and
2 values in column C. For example, "z" shows up three times in column A.
Respectively "1" and "2" show up. That would count as one. Also, "q"
exists twice in column A. Again respectively "1" and "2" shows up. That
would also count as one. So the result for this formula would be 2 for my
example. However, my data is much more.

Any ideas.

Note, the values in column A are unknown. The number of times each value
may show could be as many as 4.


Thanks,
Paul

--

 

[Guest]

=SUM(N(FREQUENCY(IF((C1:C6=1)+(C1:C6=2),MATCH(A1:A6,A1:A6,0)),MATCH(A1:A6,A1:A6,0))>0))

ctrl+shift+enter, not just enter

 

[Guest]

Probably a shorter way, but I'll take a shot at it:

=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(A1:A6&{1,2},A1:A6&C1:C6,0)),{1;1})>1),--(MATCH(A1:A6,A1:A6,0)=ROW(A1:A6)-MIN(ROW(A1:A6))+1))

 

[Guest]

I think the OP wants a count of items that appear with both a 1 and 2 in Col
C. The results I get count how many items have either a 1 or 2 in Col C.

For example, by changing the 0 for "w" to either a 1 or 2 it will get counted.

 

[Rick Rothstein \(MVP - VB\)]

If the values in C are actually numbers, then I think this will work for
you...

=SUMPRODUCT((A1:A100=F1)*(C1:C100={1,2}))

If, on the other hand, the values in C are text, then try this instead....

=SUMPRODUCT((A1:A100=F1)*(C1:C100={"1","2"}))

And if the data in C could be either numbers or text, then try it this
way...

=SUMPRODUCT((A1:A100=F1)*(C1:C100={1,2,"1","2"}))

Of course, change the range to encompass the maximum row number that might
one day contain data.

Rick