Counting
Posted by Lewis on May 08, 2001 7:22 PM
Hey guys, I need to be able to count while crossreferencing, please check out an example here and get back to me
ftp://24.9.236.152/pub/help.xls
thanx all
-Lewis
Posted by Mark W. on May 08, 2001 8:59 PM
The following array formula works:
{=SUM((FREQUENCY(MATCH(A3:A12,A3:A12,0)*B3:B12,ROW(INDIRECT("1:"&COUNTA(A3:A12))))>0)+0)}
The only question is: "Have I overengineered this thing?"
Posted by Mark W. on May 08, 2001 9:13 PM
Found a bug...look'n for a fix
If B3 is set to 0 it still returns 3!
Posted by Mark W. on May 08, 2001 9:25 PM
Okay, this should do it!
{=SUM((FREQUENCY(IF(B3:B12,MATCH(A3:A12,A3:A12,0),""),ROW(INDIRECT("1:"&COUNTA(A3:A12))))>0)+0)}
Posted by Lewis on May 09, 2001 3:39 PM
Thanks Mark
Thanx bud, that worked like a charm :-D
