Count

[Louja]

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

 

[Louja]

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

 

[Bob Phillips]

You can count the individuals with

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

 

[Louja]

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

 

[Bob Phillips]

Why didn't you say so?

Use this array formula

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

 

[Louja]

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

 

[Louja]

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

 

[Louja]

All works now - thank you very much.