Count Distinct Cells that meet a criteria. - Max Points

crimper
crimper used Ask the Experts™
on
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:H15,H5:H15)))

But not then applying the condition on the Division column as well....

Help please!

JP







Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
In the next free column put:

=COUNTIF(B:B,B1)

and copy all the way down to the end of the data.
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

Author

Commented:
Hi Patrick

Thats not what I am after....  key point is working out the Distinct count of styles - for the specific Division.

JP
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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

Author

Commented:
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
Most Valuable Expert 2011
Top Expert 2011

Commented:
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

Author

Commented:
Just spotted that one myself Rory.... silly me....
Top Expert 2008
Commented:
JP,

Use this array formula. It assumes the style is in A1:

=SUM(IF(FREQUENCY(IF((LEN(I$5:I$100)>0)*(H$5:H$100=A1),MATCH(I$5:I$100,I$5:I$100,0),""),IF((LEN(I$5:I$100)>0)*(H$5:H$100=A1),MATCH(I$5:I$100,I$5:I$100,0),""))>0,1))

Kevin
Top Expert 2008

Commented:
I meant that it assumes the division is in A1.

Kevin

Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial