PDA

View Full Version : Solved: Finding % usage of a word - w/filtering



steelstorm
06-21-2013, 10:49 AM
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)

mikerickson
06-22-2013, 09:30 AM
Something like
=COUNTIF($D:$D, "Ted")/COUNTA($D:$D)

should work.

GarysStudent
06-22-2013, 01:23 PM
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)