kapeller
Hi

I seek some advice in relation to the formula below.
CODE
=COUNT(IF(FREQUENCY(MATCH(H6:H246,H6:H246,0),MATCH(H6:H246,H6:H246,0))>0,1))

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
norie
Lou

What's the purpose of the formula?
kapeller
Hi Norie

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
norie
Lou

Perhaps I'm being thick, but couldn't you use COUNTIF for that?
kapeller
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
JonSmith
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

CODE
=SUM(IF(FREQUENCY(MATCH(H6:H246,H6:H246,0),MATCH(H6:H246,H6:H246,0))>0,1,0))

(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

CODE
=SUM(IF(H6:H246<>"",1/COUNTIF(H6:H246,H6:H246)))

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.
kapeller
Hi Jon

I have learnt a little more
QUOTE
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.

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

I am very greatfull

norie
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.
JonSmith
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.
kapeller
Hi Jon