If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need Formula the yields same count as my multicolumn filter
When I filter a multi column table, one column "Pledge"
contains only "Y" or (blank). I filter this column to ="Y". The second column "Grades" contain 1 or more of 10 different text values separated by commas(ex. {pk-ka} {pk- ka, 1-2b, 5} ). I filter "Grades" using contains. The information bar at the bottom shows how many in the specified grade have pledged. I need this number to calculate a percentage. I currently have to do the filter then manually enter the number into the % calc. There has to be away to automate this. Can anyone help. Would I have been better off with multiple grade columns containing only 1 value? I've tried nested ifs, sumif, countif, pivot tables with no success. Countif has a feature that allows you to use wildcard(*) to indicate that the text can be anywhere in the cell but this only gives the total without the other condition. Nested if, sumif and sumproduct didn't work either I think because they do not recognize the use of wildcards to define the acceptible value in "gades. |
#2
|
|||
|
|||
Need Formula the yields same count as my multicolumn filter
If I've read you correctly, here's one way which might work:
Assume you have in Sheet1, in A1:B6 Pledge...Grades Y...........pk-ka blank.....pk-ka Y...........pk-ka, 1-2b, 5 Y...........pk-ka, 1-2b, 6 Y...........pk-ka In Sheet2 ----------- Set up the table below in cols A & B where the full list of all possible* text values for "Grades" in Sheet1 is listed in A2 down, and you have in B1: Y [ *presumes a ready list is available. If not, use Data Filter Advanced filter "unique records only" feature to extract from the "Grades" col in Sheet1 to another col in Sheet1, then copy paste in Sheet2, A2 down (steps given below) ] Grade..................Y pk-ka...................? pk-ka, 1-2b, 5......? pk-ka, 1-2b, 6......? etc Put in B2: =SUMPRODUCT((Sheet1!$A$2:$A$6=B$1)*(Sheet1!$B$2:$B $6=A2)) Copy B2 down col B. Col B will return the count of each "Grade" item which pledged (i.e. indicated "Y" under "Pledge" col in Sheet1) Adjust the ranges to suit. Note that the ranges have to be identical (i.e.: A1:A6, B1:B6) and you can't use entire columns (e.g.: A:A, B:B) in SUMPRODUCT ---------------------------- If instead of the count, you want the % of records which pledged per "Grade" item [the "info" you see at the bottom status bar in autofilter mode-e.g.: "2 of 5 records found"] Put instead in B2 (of Sheet2): =SUMPRODUCT((Sheet1!$A$2:$A$6=B$1)*(Sheet1!$B$2:$B $6=A2))/SUM(COUNTA(Sheet1! $A$2:$A$6),COUNTBLANK(Sheet1!$A$2:$A$6)) Copy B2 down col B. ------------------------------------------------------------------- To extract a "uniques" list from the Grades col in Sheet1 -------------------------------------------------------------------- Assume col label (eg: Grades) is in B1, data in B2 downwards Select col B Click Data Filter Advanced Filter In the dialog box: ------------------- Check "Copy to another location" Put for "Copy to:" : X1 (say) Check "Unique records only" Click OK The "uniques" list will appear in col X -- Rgds Max xl 97 ---------------------------------- Use xdemechanik atyahoodotcom for email ----------------------------------------- wrote in message ... When I filter a multi column table, one column "Pledge" contains only "Y" or (blank). I filter this column to ="Y". The second column "Grades" contain 1 or more of 10 different text values separated by commas(ex. {pk-ka} {pk- ka, 1-2b, 5} ). I filter "Grades" using contains. The information bar at the bottom shows how many in the specified grade have pledged. I need this number to calculate a percentage. I currently have to do the filter then manually enter the number into the % calc. There has to be away to automate this. Can anyone help. Would I have been better off with multiple grade columns containing only 1 value? I've tried nested ifs, sumif, countif, pivot tables with no success. Countif has a feature that allows you to use wildcard(*) to indicate that the text can be anywhere in the cell but this only gives the total without the other condition. Nested if, sumif and sumproduct didn't work either I think because they do not recognize the use of wildcards to define the acceptible value in "gades. |
#3
|
|||
|
|||
Need Formula the yields same count as my multicolumn filter
If you are using autofilter you can use the subtotal function, to get a
percentage use =SUBTOTAL(3,MyRange)/COUNTA(MyRange) -- Regards, Peo Sjoblom wrote in message ... When I filter a multi column table, one column "Pledge" contains only "Y" or (blank). I filter this column to ="Y". The second column "Grades" contain 1 or more of 10 different text values separated by commas(ex. {pk-ka} {pk- ka, 1-2b, 5} ). I filter "Grades" using contains. The information bar at the bottom shows how many in the specified grade have pledged. I need this number to calculate a percentage. I currently have to do the filter then manually enter the number into the % calc. There has to be away to automate this. Can anyone help. Would I have been better off with multiple grade columns containing only 1 value? I've tried nested ifs, sumif, countif, pivot tables with no success. Countif has a feature that allows you to use wildcard(*) to indicate that the text can be anywhere in the cell but this only gives the total without the other condition. Nested if, sumif and sumproduct didn't work either I think because they do not recognize the use of wildcards to define the acceptible value in "gades. |
Thread Tools | |
Display Modes | |
|
|