# Separating values

#### Blake0920

##### Board Regular
Trying to modify an existing formula where I can segregate different options within the same column and have them return a percentage depending which value was chosen.

The current formula is set up to calculate whether "S" has been selected. I need to add "R", "E", and "O"

This is the current formula. =SUMPRODUCT(SUBTOTAL(3,OFFSET(D4,ROW(D4:D48)-ROW(D4),0,1,1))*ISNUMBER(SEARCH(", "&TRANSPOSE(D7)&",",", "&D4:D48&",")))/SUMPRODUCT(SUBTOTAL(3,OFFSET(D4,ROW(D4:D48)-ROW(D4),0,1,1))*(LEN(D4:D48)-LEN(SUBSTITUTE(D4:D48,",",""))+(D4:D48<>"")))

Thank you.

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Anyone been able to help?

?

i think the formula can be reduced to this
i'm not sure if you have to replace the ";" with "," in {"S";"e";"R";"O"}
Map1
CDEF
2reduced formula53%
3multiple entries80%
4E
5
6e
7R
8
9S
10S
11SSS
12r
13
14
Cell Formulas
RangeFormula
F1F1=SUMPRODUCT(SUBTOTAL(3,OFFSET(D4,ROW(D4:D48)-ROW(D4),0,1,1))*ISNUMBER(SEARCH(", "&TRANSPOSE(D7)&",",", "&D4:D48&",")))/SUMPRODUCT(SUBTOTAL(3,OFFSET(D4,ROW(D4:D48)-ROW(D4),0,1,1))*(LEN(D4:D48)-LEN(SUBSTITUTE(D4:D48,",",""))+(D4:D48<>"")))
F2F2=COUNTIF(\$D\$4:\$D\$48,D7)/COUNTA(\$D\$4:\$D\$48)
F3F3=SUM(COUNTIF(\$D\$4:\$D\$48,{"S";"e";"R";"O"}))/COUNTA(\$D\$4:\$D\$48)

i think the formula can be reduced to this
The subtotal / offset arrays in the formula will be needed as the OP's requirement is to look at a filtered range, something that your shorter formulas will not do.

This will give the count, note that cells which contain 2 (or more) of the criteria will be counted 2 (or more) times, once for each matching item in the cell. If a cell containing multiple matches should only be counted once then it will be necessary to include a frequency array in the formula.
Excel Formula:
``=SUMPRODUCT(SUBTOTAL(3,OFFSET(D4,ROW(D4:D48)-ROW(D4),0,1,1))*ISNUMBER(SEARCH(", "&{"S","R","E","O"}&",",", "&D4:D48&",")))``

On the assumption that the cell containing a value of 50 at the foot of the second screen capture is a count of the visible results without criteria, the above can be divided by that figure to give the result required rather than doubling the length of this formula.

Replies
1
Views
186
Replies
1
Views
209
Replies
5
Views
538
Replies
2
Views
271
Replies
6
Views
275

### Forum statistics

1,203,030
Messages
6,053,130
Members
444,640
Latest member
Dramonzo ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back