Hi,

On my file I get a list of accounts. I have a look up which lets me
know which account belongs to which individual.

I need to know how many different individuals there are each day.
There can be a lot of change with new accounts etc so was thinking of
doing a pivot table and somehow using a COUNT function but am not sure
if this is going to work or if there is a better alternative.

Thanks

Louisa

Also, I need to be able to know which group each individual belongs to
(which I also assign via a lookup) so that I can split headcount by
group and also office.

Thanks again

Louisa

You can count the individuals with

=SUMPRODUCT(--(B1:B100<""),1/(COUNTIF(B1:B100,B1:B100&""))

--

HTH

Bob

"Louja" wrote in message
...
Hi,

On my file I get a list of accounts. I have a look up which lets me
know which account belongs to which individual.

I need to know how many different individuals there are each day.
There can be a lot of change with new accounts etc so was thinking of
doing a pivot table and somehow using a COUNT function but am not sure
if this is going to work or if there is a better alternative.

Thanks

Louisa

I get that formula and it works well for all the individuals but say I
wanted to count how many different individuals were in London / Paris
(see below) how would I get this to work

Name Group
Adam Smith London
John Pickles Paris
Lee Adams Paris
Ben Jones London
Adam Smith London
John Pickles Paris


Thanks again

Louisa

Why didn't you say so?

Use this array formula

=SUM(--(FREQUENCY(IF(B2:B100="Paris",MATCH(A2:A100,A2:A10 0,0)),ROW(INDIRECT("1:"&ROWS(A2:A100))))0))

--

HTH

Bob

"Louja" wrote in message
...
I get that formula and it works well for all the individuals but say I
wanted to count how many different individuals were in London / Paris
(see below) how would I get this to work

Name Group
Adam Smith London
John Pickles Paris
Lee Adams Paris
Ben Jones London
Adam Smith London
John Pickles Paris


Thanks again

Louisa

I didn't say so as I only just figured that is what I wanted......

I have tried to use this formula and it comes back with 0

On 12 Mar, 09:41, "Bob Phillips" wrote:
Why didn't you say so?

Use this array formula

=SUM(--(FREQUENCY(IF(B2:B100="Paris",MATCH(A2:A100,A2:A10 0,0)),ROW(INDIRECT ("1:"&ROWS(A2:A100))))0))

--

HTH

Bob

"Louja" wrote in message

...



I get that formula and it works well for all the individuals but say I
wanted to count how many different individuals were in London / Paris
(see below) how would I get this to work

Name Group
Adam Smith London
John Pickles Paris
Lee Adams Paris
Ben Jones London
Adam Smith London
John Pickles Paris

Thanks again

Louisa

All works now - thank you very much.