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:H15,H5:H15)))

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

Help please!

JP







LVL 3
crimperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

patrickabCommented:
In the next free column put:

=COUNTIF(B:B,B1)

and copy all the way down to the end of the data.
patrickabCommented:
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
crimperAuthor Commented:
Hi Patrick

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

JP
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

patrickabCommented:
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
crimperAuthor 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
Rory ArchibaldCommented:
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
crimperAuthor Commented:
Just spotted that one myself Rory.... silly me....
zorvek (Kevin Jones)ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zorvek (Kevin Jones)ConsultantCommented:
I meant that it assumes the division is in A1.

Kevin
crimperAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.