Count

  • Thread starter Thread starter Louja
  • Start date Start date
L

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
 
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&""))
 
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:A100,0)),ROW(INDIRECT("1:"&ROWS(A2:A100))))>0))
 
Back
Top