Hi

I seek some advice in relation to the formula below.

The issue I am need to address, if possible, is that if the data within the range is less than H6 to H246 the value returned is zero “0”.

Am I stuck with this or is there a solution for this.


Many thanks

Lou

What's the purpose of the formula?

Hi Norie

Sorry for not providing more information

What the formula is meant to do is match and then count how many occurrences a name appears in the range

EG Kapeller, Smith Browne and so on

See attached


Click to view attachment

Lou

Perhaps I'm being thick, but couldn't you use COUNTIF for that?

Hi Norie

Your are not thick. Its in my explaination.


It should only count kapeller as one ocurrence, browne occurence and so on.

Hope this helps

Hi guys,

Correct me if I am wrong but you want to count the number of unique cells (in this case names) within a range.

If so you have the choice of this


(this will fail if any of the cells are blank however)


or an array formula, I am sure know what this are but for anyone who hasn't heard of them I won't go into detail (what Google is for) but to enter them you must type them out in the formula bar and then press Ctrl+Shift+Enter to save them. It will be wrapped in {} if you do it correctly.

This solution gets around the blanks problem



I cannot claim credit for these solutions btw, other people who are abit better at writing formula's worked it out before I got there.

Hi Jon

Thank you for your help.

I have learnt a little more


It does not matter who wrote the solution, you were able provide it to me.

I am very greatfull

Lou

Should have figured it might have been for unique names but it was a bit late when I posted last night.

FWIW here's a non-array formula.

Happy to help Lou and I'm glad I understood what you were after correctly.

See this for more info on array formula's. They can be useful and have a place but they are not exactly efficient when they run. Often a DCount or DSum will work better.

Hi Jon

Thanks for this additional information.

Hi Norie

Many thanks for your input to this issue.