# Extracting/counting unique entries does not work

Posted by Veronica P. on November 01, 2000 8:51 AM

I have in E1 the following formula (array) found at cpearson.com and Microsoft web site tring to count unique entries:

=SUM(IF(FREQUENCY(IF(LEN(D11:D33)>0,MATCH(D11:D33,D11:D33,0),""),IF(LEN(D11:D33)>0,MATCH(D11:D33,D11:D33,0),""))>0,1))

In D11:D33 I have 020 which is the rezult of formula

=IF(B11="","",MID(B11,4,3)), IF(B12="","",MID(B12,4,3))a.s.o.

In B11, B12 ... I have M40020B01010, M40020B01011 aso but sometimes only B11 to B20 have codes, the rest are blank.

020 from M40020 are numbers. Sometimes that code can be alfa. In my range (D11:D33), as I said, I have the formula =IF(Bxx="","",MID(Bxx,4,3)) so the rezult can be 020 , 3 alfa (abc) or "". The cells are formated as General; I changed them to almost any type possible with no effect.

The array formula returns #N/A error. If I copy/paste value D11:D33 it works. It has something to do with the fact that the range is the result of another formula I guess. I tried to change the array formula using functin T or Value in front of the referece range with no success. Anybody can help. Unfortunately my spreadsheet is huge with many links so I'll not be able to email it. Thank you.