MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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