Formulate % with named range

[The Grim Discovery]

Hiya

This table is representative of a larger data set I'm working with. I want to calculate the number of yes / (yes+no)%. I wondered if I could use a named range by highlighting only rows of data which were relevant and ignore those that were blank or contained numerical .

But Excel didn't like my new formula (where namedrange is A1:G1, A3:G3 & A5:G5).

=(COUNTIF(namedrange,"yes")/(COUNTIF(namedrange,"yes")+COUNTIF(namedrange,"no"))%

Can this be done?
Thanks

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD]n/a[/TD]
[TD]yes[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD]n/a[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]25[/TD]
[TD]40[/TD]
[TD]46[/TD]
[TD]38[/TD]
[TD]71[/TD]
[TD]25[/TD]
[TD]47[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD]yes[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]no[/TD]
[TD]n/a[/TD]
[TD]yes[/TD]
[TD]yes[/TD]
[TD]n/a[/TD]
[TD]no[/TD]
[TD]no[/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

ignore those that were blank or contained numerical .

Hi, COUNTIFs() will ignore values that do not match the criteria so can't you just include the whole range. i.e.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1	yes	no	n/a	yes	yes	no	n/a		57%
2	25	40	46	38	71	25	47
3	yes	no	n/a	n/a	n/a	yes	yes
4
5	no	n/a	yes	yes	n/a	no	no
Sheet1

Cell Formulas
Range	Formula
I1	=COUNTIFS(A1:G5,"YES")/SUM(COUNTIFS(A1:G5,{"Yes","No"}))

 

[The Grim Discovery]

FormR - cheers for that. But now realised that some rows of data contain 'Yes' and 'No's which I don't want to include the formula. Didn't spot that at the time of my original post.

 

[FormR]

You can't use non-contiguous ranges with COUNTIFs() - is there anything about those rows that we use to logically determine if they should be included or not. Maybe some common text in a different column, or maybe it's every other row? or anything else?

 

[The Grim Discovery]

I'll have a look - got a feeling it's going to be horribly random. Thanks for your support and time with this.

 

[FormR]

got a feeling it's going to be horribly random.

Maybe you could make use of a couple of helper columns to count a specific rows yes's and no's but only manually applied to the rows of interest. A tad messy but sounds like any alternatives might be even more so.

For example row 5 here would not contain the formulas:

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L
1									Yes's	No's
2	yes	no	n/a	yes	yes	no	n/a		3	2		53%
3	yes	no	n/a	n/a	n/a	yes	yes		3	1
4	no	n/a	yes	yes	n/a	no	no		2	3
5	no	n/a	yes	yes	n/a	no	no
6	no	n/a	yes	yes	n/a	no	no		2	3
Sheet1

Cell Formulas
Range	Formula
I2	=COUNTIFS(A2:G2,"Yes")
J2	=COUNTIFS(A2:G2,"No")
L2	=SUM(I2:I6)/SUM(I2:J6)

 

[markmzz]

Maybe the formula below can help you:

=SUMPRODUCT((INDEX(namedrange,,,1)="Yes")+(INDEX(namedrange,,,2)="Yes")+(INDEX(namedrange,,,3)="Yes"))/
SUM(COUNTIF(INDEX(namedrange,,,1),{"Yes";"No"})+COUNTIF(INDEX(namedrange,,,2),{"Yes";"No"})+COUNTIF(INDEX(namedrange,,,3),{"Yes";"No"}))

Markmzz