Count
in [Functions]
|
From: Louja on 9 Mar 2010 05:16 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
From: Louja on 9 Mar 2010 06:02 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
From: Bob Phillips on 9 Mar 2010 06:43 You can count the individuals with =SUMPRODUCT(--(B1:B100<>""),1/(COUNTIF(B1:B100,B1:B100&"")) -- HTH Bob "Louja" <louisa.c.thompson(a)googlemail.com> wrote in message news:6c5ab34c-09a5-4510-b0df-dc98bc42f4fb(a)j27g2000yqn.googlegroups.com... > 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
From: Louja on 12 Mar 2010 04:26 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
From: Bob Phillips on 12 Mar 2010 04:41
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)) -- HTH Bob "Louja" <louisa.c.thompson(a)googlemail.com> wrote in message news:e9b0088f-5ed3-404c-aafb-cd3ec49d2f92(a)33g2000yqj.googlegroups.com... >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 |