Count Query Excel 2010

[philb99]

Hi - Would like to know how you can count names in column please as I am struggling.
Thanks

 

[mole999]

if you have a list of all the names you can use COUNTIF

 

[philb99]

Hi - thanks but I just have a random selection of names like, smith, brown, tom, frank with a heading OF Name-

Countif asks for a range (which is ok) but then Criteria which I am now stuck again

 

[Fluff]

Something like
=SUMPRODUCT(1/COUNTIF(A2:A11,A2:A11))

 

[philb99]

thanks - the issue I can now see is - My names are in two separate columns for example - Col A = Mike and Col B = Smith.

SumProduct does a great job in counting different names but if I have a Mike Smith and a Mike Brown it will only count one Mike, likewise for the same surname.

Is there anyway to merge to the the two columns. Presently they are structures as

Col A = Either Jane Mrs / Mr
Col B = Either Smith or Smith, Jane

 

[Fluff]

Can you please supply some realistic samples of your data?

 

[philb99]

[TABLE="width: 198"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]First name[/TD]
[TD]Last name[/TD]
[/TR]
[TR]
[TD]Mrs[/TD]
[TD]Brown, Lisa[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Smith[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Jones[/TD]
[/TR]
[TR]
[TD]Mr[/TD]
[TD]Delph, Clive[/TD]
[/TR]
[TR]
[TD]Stuart[/TD]
[TD]Round[/TD]
[/TR]
</tbody>[/TABLE]

Hi - does this help, the length of the list could be 1000's

 

[Fluff]

Maybe =SUMPRODUCT(1/COUNTIFS(A2:A7,A2:A7,B2:B7,B2:B7))

 

[philb99]

Perfect - thank you for you help and support - any idea how I would merge the two columns in order that I can read Mrs Lisa Brown / Tom Smith / Fred Jones / Mr Clive Delph / Stuart Round in one cell please

 

[Fluff]

As that is a completely different question, you will need to start a new thread.