• July 17, 2017
    Announcement: New site design for Microsoft Community

    In July, Microsoft will roll out the first of ongoing site improvements aimed to modernize Microsoft Community and help customers get the most out of their community experience.

    • During the roll out period, you may see the old or new site design depending on your location

    • We expect the roll out to finish by 31 July

    Note: Past private message conversations will not move to the new site design. Please save any private messages you would like to keep.

     Learn more about the upcoming site improvements in this thread.

    Thank you for being part of Microsoft Community!

 
Question
58 views

count of unique text-value cells in one column that also have specific text values in two other columns

AnnVoss asked on

I have a worksheet that has 28 columns, with data in almost 11,000 rows.

Column D is "name" which is a text string

Column X has text values of either Yes or No

Column AA has a 4 character text string (e.g. ABCD - only 6 possible values in this column, representing business units)

I need to count the number of unique values in D for each unique value in AA, e.g. ABCD  has 5 unique names, EFGH has 30 unique names, etc. This is UniqueNamesPerBusinessUnit

Next, I need to count the number of unique values in D that also have the value Yes in X, for each value in AA. This is UniquePositiveNamesPerBusinessUnit

This will allow me to then get a "score" percentage of UniquePositiveNamesPerBusinessUnit/UniqueNamesPerBusinessUnit

I'm more than willing to make intermediate calculations in separate cells - one for each business unit for UniqueNames and then UniquePositiveNames?

I've tried samples that use matches within frequency, but only ever get #N/A values when copying the formula from the office site examples, even just to get a unique list of names, but I need to also consider the extra parameters of business unit, and then the Yes/No in column X, and at that point my eyes glaze over.

Ideas?

1 person had this question

Abuse history


The answered status icon Answer
HansV MVP replied on

Let's say the data are on a sheet named Data, from row 2 to row 11,000.

On another sheet, enter the text Business Unit in A1, UniqueNames in B1, UniquePositiveNames in C1, and Score in D1.

Enter the unique business units (ABCD etc.) in A2:A7.

In B2, enter the following array formula, confirmed with Ctrl+Shift+Enter:

=SUM(IF(FREQUENCY(IF(Data!$AA$2:$AA$11000=$A2,MATCH(Data!$D$2:$D$11000,Data!$D$2:$D$11000,0)),IF(Data!$AA$2:$AA$11000=$A2,MATCH(Data!$D$2:$D$11000,Data!$D$2:$D$11000,0)))>0,1))

In C2, enter the following array formula, confirmed with Ctrl+Shift+Enter:

=SUM(IF(FREQUENCY(IF((Data!$AA$2:$AA$11000=$A2)*(Data!$X$2:$X$11000="Yes"),MATCH(Data!$D$2:$D$11000,Data!$D$2:$D$11000,0)),IF((Data!$AA$2:$AA$11000=$A2)*(Data!$X$2:$X$11000="Yes"),MATCH(Data!$D$2:$D$11000,Data!$D$2:$D$11000,0)))>0,1))

In D2, enter the following 'ordinary' formula

=C2/B2

and format D2 as a percentage.

Select B2:D2 and fill down to B7:D7.

Kind regards, HansV
www.eileenslounge.com
1 person found this helpful

Abuse history


progress