Count duplicate text entries and ignore errors

[raaz00]

In the range B5:B123, I have text data that is formula driven from a VLOOKUP function (so some values return #N/A). I need a formula that will count the duplicate text entries, but ignores the error #N/A.

**I know how to make this work using a PivotTable, but that requires filtering and refreshing when new entries are added. This is a template that will be used for daily data entry, so I need something that can do this automatically and quickly.

Thanks in advance.

[daddylonglegs]

Can you explain what you mean by "count the duplicate text entries". Do you mean you want to count the number of different text entries....or something else?

My assumption is that if you have these 6 entries, "xyz","#N/A","xyz","xyz","#N/A","abc"....then the count should be 2 ["xyz" and abc", both counted once only] - is that right?

[raaz00]

Can you explain what you mean by "count the duplicate text entries". Do you mean you want to count the number of different text entries....or something else?

My assumption is that if you have these 6 entries, "xyz","#N/A","xyz","xyz","#N/A","abc"....then the count should be 2 ["xyz" and abc", both counted once only] - is that right?

Thank you for the reply, daddylonglegs.

In your example, I would want the count to be 1. "xyz" is the only text that is duplicated/repeated. I should have been more specific in my question. Only when a text entry is duplicated/repeated do I want it counted.

[benishiryo]

hi raaz00, welcome to the forum. maybe an array formula like this?

Please Login or Register  to view this content.

you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER

[raaz00]

hi raaz00, welcome to the forum. maybe an array formula like this?

Please Login or Register  to view this content.

you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER

Thank you for the reply, benishiryo.

The workbook is saved on a server at my job, and since I don't work weekends, I can't test your formula. I'll definitely try it out on Monday and get back to you. Thanks for the help.

[raaz00]

hi raaz00, welcome to the forum. maybe an array formula like this?

Please Login or Register  to view this content.

you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER

Beautiful! Thanks so much for your help. It works exactly how I needed it too!