crimper
asked on
Count Distinct Cells that meet a criteria. - Max Points
Hi All
Been a while since I have been on here.... and have a question for a change...
Consider the data:
Divison Style
1 CLOTHING50-250 1000065
1 CLOTHING500-1000 1000065
2 NLA<50 1000464
2 NLA<50 1000508
2 NLA<50 1000518
2 NLA50-250 1000552
2 NLA<50 1000552
3 MENSWEAR<50 1000914
3 MENSWEAR<50 1000914
2 NLA250-500 1001012
2 NLA250-500 1001012
I need to work out the following
A count of all the Distinct Styles - for a given division.
So for example:
1 CLOTHING50-250 - has only one distinct Style - so the result is 1.
2 NLA<50 - has four styles - and they are all distinct - so the result is also 1
2 NLA250-500 - has two styles - but the number of distinct styles is 1
Hopefully this makes sense....
Now I have refreshed my mind on arrays and sumproduct etc etc... so almost have what I want... but not quite....
I have no problem counting the distinct number of styles - =SUMPRODUCT((1/COUNTIF(H5:
But not then applying the condition on the Division column as well....
Help please!
JP
Been a while since I have been on here.... and have a question for a change...
Consider the data:
Divison Style
1 CLOTHING50-250 1000065
1 CLOTHING500-1000 1000065
2 NLA<50 1000464
2 NLA<50 1000508
2 NLA<50 1000518
2 NLA50-250 1000552
2 NLA<50 1000552
3 MENSWEAR<50 1000914
3 MENSWEAR<50 1000914
2 NLA250-500 1001012
2 NLA250-500 1001012
I need to work out the following
A count of all the Distinct Styles - for a given division.
So for example:
1 CLOTHING50-250 - has only one distinct Style - so the result is 1.
2 NLA<50 - has four styles - and they are all distinct - so the result is also 1
2 NLA250-500 - has two styles - but the number of distinct styles is 1
Hopefully this makes sense....
Now I have refreshed my mind on arrays and sumproduct etc etc... so almost have what I want... but not quite....
I have no problem counting the distinct number of styles - =SUMPRODUCT((1/COUNTIF(H5:
But not then applying the condition on the Division column as well....
Help please!
JP
crimper,
The other more complex answer is to use a pivot table. Here's a file showing both methods:
http://www.asdy88.dsl.pipex.com/Experts%20Exchange/crimper01.xls
Hope that helps
Patrick
The other more complex answer is to use a pivot table. Here's a file showing both methods:
http://www.asdy88.dsl.pipex.com/Experts%20Exchange/crimper01.xls
Hope that helps
Patrick
ASKER
Hi Patrick
Thats not what I am after.... key point is working out the Distinct count of styles - for the specific Division.
JP
Thats not what I am after.... key point is working out the Distinct count of styles - for the specific Division.
JP
crimper,
If you like the look of the pivot table please let me know and I will give you step by step instructions - if you've not used one before.
Patrick
If you like the look of the pivot table please let me know and I will give you step by step instructions - if you've not used one before.
Patrick
ASKER
Just realised - made a slight mistake with my question....
Divison Style
1 CLOTHING50-250 1000065
1 CLOTHING500-1000 1000065
2 NLA<50 1000464
2 NLA<50 1000508
2 NLA<50 1000518
2 NLA50-250 1000552
2 NLA<50 1000552
3 MENSWEAR<50 1000914
3 MENSWEAR<50 1000914
2 NLA250-500 1001012
2 NLA250-500 1001012
I need to work out the following
A count of all the Distinct Styles - for a given division.
So for example:
1 CLOTHING50-250 - has only one distinct Style - so the result is 1.
2 NLA<50 - has four styles - and they are all distinct - so the result is 4 (NOT 1 LIKE I SAID ABOVE!!)
2 NLA250-500 - has two styles - but the number of distinct styles is 1
SORRY TO CONFUSE
JP
Divison Style
1 CLOTHING50-250 1000065
1 CLOTHING500-1000 1000065
2 NLA<50 1000464
2 NLA<50 1000508
2 NLA<50 1000518
2 NLA50-250 1000552
2 NLA<50 1000552
3 MENSWEAR<50 1000914
3 MENSWEAR<50 1000914
2 NLA250-500 1001012
2 NLA250-500 1001012
I need to work out the following
A count of all the Distinct Styles - for a given division.
So for example:
1 CLOTHING50-250 - has only one distinct Style - so the result is 1.
2 NLA<50 - has four styles - and they are all distinct - so the result is 4 (NOT 1 LIKE I SAID ABOVE!!)
2 NLA250-500 - has two styles - but the number of distinct styles is 1
SORRY TO CONFUSE
JP
Hi,
Perhaps I'm being dumb, but why is the answer 1 and not 4 for NLA<50? Surely there are 4 distinct styles?
Regards,
Rory
Perhaps I'm being dumb, but why is the answer 1 and not 4 for NLA<50? Surely there are 4 distinct styles?
Regards,
Rory
ASKER
Just spotted that one myself Rory.... silly me....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I meant that it assumes the division is in A1.
Kevin
Kevin
ASKER
Kevin you are a GENIUS!!!
This has been bugging me all day....
Me one happy bunny now....!
Now I just have to get my head round what the calc is actually doing....
Interestingly - I did stumble across a similar example on the web to calculate the number of distinct values in a single column using the frequency function - but not with additional criteria.
Hopefully this answer will prove useful to other people too!
Thanks again
JP
This has been bugging me all day....
Me one happy bunny now....!
Now I just have to get my head round what the calc is actually doing....
Interestingly - I did stumble across a similar example on the web to calculate the number of distinct values in a single column using the frequency function - but not with additional criteria.
Hopefully this answer will prove useful to other people too!
Thanks again
JP
=COUNTIF(B:B,B1)
and copy all the way down to the end of the data.