Identifying unique values among duplicates
Try this array formula** entered on Sheet1:
=COUNT(1/FREQUENCY(IF((H2:H9=Sheet2!A1)*(I2:I9=Sheet2!C1),A 2:A9),A2:A9))
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
--
Biff
Microsoft Excel MVP
"bob" wrote in message
...
If sheet2 cols A and C match the value in sheet1 cols H and I, I want to
return the number of unique values in sheet 1 column A. Can anyone provide
a
formula for accomplishing this:
The following example should yield a result of 2 for the Arenas/Bibby
combination because there are two rows in sheet1 where Arenas is in colH
and
Bibby in colI, AND the date in colA is unique (10/29/08 and 10/30/08):
sheet2
colA colC
Arenas Bibby
sheet1
colA colH colI
10/29/08 Butler Simmons
10/29/08 Arenas Bibby
10/29/08 Thomas Brown
10/30/08 Arenas Bibby
10/30/08 Stevens Hale
10/30/08 Arenas Bibby
10/30/08 Stevens Simmons
10/31/08 Arenas Simmons
Thanks,
Bob
|