Countifs multiple criteria in a range and a column error

[Uncanny_Wolf]

Hi fellow Excel friends, can someone help me figure out why this equation doesn't work:

=SUM(COUNTIFS(F5:AN99,{"Y1","Y2","M"},$C$5:$C$99,"S"))/SUM(COUNTIFS(F5:AN99,{"Y1","Y2","M","N","NR"},$C$5:$C$99,"S"))

I am trying to work out a percentage of cells that meet my criteria, so essentially want to know the number of cells that contain Y1 or Y2 etc... in the range F5:AN99 AND also have a letter S on the corrosponding row in column C.

Can anyone help me?

Many thanks

 

[Toadstool]

Hi Uncanny_Wolf,

The criteria range for COUNTIFS must all be the same size so F5:AN99 can't be used with the single column $C$5:$C$99

I'm sure somebody will be along with a more elegant solution but until then I believe this will do what you ask.
NOTE: All cells not shown are empty for brevity.

Uncanny_Wolf.xlsx
	A	B	C	D	E	F	G	H	I
1
2
3						66.67%
4
5			S			Y1	N	Y2
6			X			N	M	N
7			S			M	NR	M
8			X			N	NR	N
9			S			Y2	N	Y1
10
Sheet1

Cell Formulas
Range	Formula
F3	F3	=(SUMPRODUCT(--($F$5:$AN$99="Y1")*($C$5:$C$99="S"))+SUMPRODUCT(--($F$5:$AN$99="Y2")*($C$5:$C$99="S"))+SUMPRODUCT(--($F$5:$AN$99="M")*($C$5:$C$99="S")))/(SUMPRODUCT(--($F$5:$AN$99="Y1")*($C$5:$C$99="S"))+SUMPRODUCT(--($F$5:$AN$99="Y2")*($C$5:$C$99="S"))+SUMPRODUCT(--($F$5:$AN$99="M")*($C$5:$C$99="S"))+SUMPRODUCT(--($F$5:$AN$99="N")*($C$5:$C$99="S"))+SUMPRODUCT(--($F$5:$AN$99="NR")*($C$5:$C$99="S")))

 

[Muhammad_Usman]

This formula might be also working for you.

Book1.xlsm
	B	C	D	E	F	G
3				0.818182
4
5
6
7
8		S			Y1	Y2
9		S			Y1	Y2
10		S			Y1	Y2
11					Y1	Y2
12		S			N	NR
13					N	Y2
14		S			N	Y2
15					N	Y2
16		S			Y1	Y2
17
Sheet3

Cell Formulas
Range	Formula
E3	E3	=SUMPRODUCT(--((F8:I23="y1")+(F8:I23="y2")+(F8:I23="M"))*(C8:C23="S"))/SUMPRODUCT(--((F8:I23="y1")+(F8:I23="y2")+(F8:I23="M")+(F8:I23="N")+(F8:I23="R"))*(C8:C23="S"))

 

[Uncanny_Wolf]

Thank you Both, for your help with these work around, really appreciate you both helping me out

 

[Toadstool]

You're welcome!

 

[Muhammad_Usman]

Thank you Both, for your help with these work around, really appreciate you both helping me out

Thank you and glad to help you.

Please mark solution. Thanks