Posted to microsoft.public.excel.worksheet.functions
|
|
Compare columns, count matches
In one cell:
{=SUM(N(NOT(ISERR(1/(COUNTIF(A1:A20,B1:B20)0)))))}
An array formula, so don't type the outer {}'s just press Shift Ctrl Enter
to enter the formula.
--
Thanks,
Shane Devenshire
"adodson" wrote:
Thank you for the extensive answer, but I need it to be contained in one cell
since this is in a report format.
"Max" wrote:
One interp & play, presuming that what you're really after is to extract the
uniques list of names within A1:B20
In A11: =IF(B1="","",B1)
Copy A11 down to A20
In D1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",ROW()))
In E1:
=IF(ROW()COUNT(D:D),"",INDEX(A:A,SMALL(D:D,ROW()) ))
Select D1:E1, copy down to E20. Col E returns the list of unique names
within A1:B20, all neatly bunched at the top.
And if you also want the count of the unique names extracted in col E:
In F1, copy down to F20:
=IF(E1="","",COUNTIF(A:A,E1))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"adodson" wrote:
I'm looking to compare two lists (a1:a10 compared to b1:b10, unnamed). Then,
return the count of original matches between the two lists. For example, if
Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once.
Any advice greatly appreciated? Thank in advance.
P.S. = this will be incorporated into an If statement.
|