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.
Posted by Celia on November 01, 2000 7:22 PM
Veronica
I have just tried the formula and it works (with the formula in D11:D33 and regardless of whether B11:B33 contains data).
If you like, I can send you the workbook (need your address).
Celia
Posted by Ivan Moala on November 01, 2000 9:38 PM
Check your range of data..... one of them may have
an error in them ( if it has a formula in it )
this would generate a N/A error.
Ivan
Posted by Veronica P. on November 02, 2000 12:27 PM
Still puzzled
I found the culprit; in a cell I have this formula which is OK, it returns the right result with no error (no error in the next ranges) but if replace that formula with Paste values I do not get the #N/A error. I guess I have to find a workarround. Thank you all.
Posted by Veronica P. on November 02, 2000 12:29 PM
Thank you Celia, it worked for me in other testing worksheet but not in the one which I have trouble with. thanks a lot. : I have in E1 the following formula (array) found at cpearson.com and Microsoft web site tring to count unique entries:
Posted by Veronica P. on November 02, 2000 12:37 PM
Sorry, forgot to paste the culprit
=IF((ROW()-(ROW(F$5)-ROW(INDIRECT("F1"))))<=(ROWS(F$5:F$100)-COUNTBLANK(F$5:F$100)),F5,IF(AND((ROW()-(ROW(F$5)-ROW(INDIRECT("F1"))))>(ROWS(F$5:F$100)-COUNTBLANK(F$5:F$100))=TRUE,OFFSET(M$5,((ROW()-(ROW(F$5)-ROW(INDIRECT("F1"))))-(ROWS(F$5:F$100)-COUNTBLANK(F$5:F$100))-1),0)=""),"",OFFSET(M$5,((ROW()-(ROW(F$5)-ROW(INDIRECT("F1"))))-(ROWS(F$5:F$100)-COUNTBLANK(F$5:F$100))-1),0))) : I have in E1 the following formula (array) found at cpearson.com and Microsoft web site tring to count unique entries:
Posted by Veronica P. on November 03, 2000 12:35 PM
I found another formula which I addapted to my case but unfortunately I can not make it work when the range has blanks also; I get #DIV/0! error. The array formula is:
=SUM(1/COUNTIF(D11:D33,D11:D33))-1
The initial formula returns error I think because some links refere to multiple ranges.
Any new help greatly appreciated. Thanks.
Posted by Ivan Moala on November 03, 2000 1:19 PM
=SUM(1/COUNTIF(D11:D33,D11:D33))-1
Use the array formula
=SUM(COUNTIF(A8:C10,A8:C10)/IF(NOT(COUNTIF(A8:C10,A8:C10)),1,COUNTIF(A8:C10,A8:C10))^2)
Ivan
Posted by Veronica P. on November 03, 2000 2:23 PM
Thanks a lot works excelent
