-
Solved: Finding % usage of a word - w/filtering
Hello,
I am currently working on a spreadsheet that has Column D that will contain only 2 words. I need to look through D and give the % that each word is used and placed in L1 and N1 like the example below.
ColD
Ted
Ted
Bill
Bill
50% Bill 50% Ted (These 2 percentages in L1 & N1)
The thing to remember is this sheet will have filtering as the data is imported from a SQL server.
I was able to find the total number of rows in another column by using Subtotal(so it works with the filtering). But when it comes to this part I'm just stuck. Maybe this can be done with Subtotal also and I'm just missing something?
Than you in advance for any assistance.....I feel I'm so close but I just can't figure it out)
-
Something like
=COUNTIF($D:$D, "Ted")/COUNTA($D:$D)
should work.
-
In F1 enter:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(D2 : D50,ROW(D2 : D50)-MIN(ROW(D2 : D50)),,1))*(D2 : D50="Ted"))
and in F2 enter:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(D2 : D50,ROW(D2 : D50)-MIN(ROW(D2 : D50)),,1))*(D2 : D50="Bill"))
and then something like:
=F1/(F1 + F2)
and
=F2/(F1 + F2)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules