Need Formula the yields same count as my multicolumn filter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
If you are using autofilter you can use the subtotal function, to get a
percentage use

=SUBTOTAL(3,MyRange)/COUNTA(MyRange)
 
Back
Top