Separating values

[Blake0920]

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.

 

[Blake0920]

Anyone been able to help?

 

[Blake0920]

?

 

[BSALV]

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
	C	D	E	F
1			your formula	53%
2			reduced formula	53%
3			multiple entries	80%
4		E
5
6		e
7		R
8
9		S
10		S
11		SSS
12		r
13
14
Blad1

Cell Formulas
Range	Formula
F1	F1	=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<>"")))
F2	F2	=COUNTIF($D$4:$D$48,D7)/COUNTA($D$4:$D$48)
F3	F3	=SUM(COUNTIF($D$4:$D$48,{"S";"e";"R";"O"}))/COUNTA($D$4:$D$48)

 

[jasonb75]

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.

=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.