If statement to sum if cell contains certain txt

[Conell8383]

[FONT=&quot]I am trying to get an if statement to sum based on certaincriteria. I have attached a screen shot for better understanding. Essentiallywhat I want to achieve is this. In my screen shot Cell U4 is the sum of CellR4:T4 This is fine but what I want to incorporate is that if Cell O4 contains"yes" then sum and show the number which is fine but if Cell O4contains "No" then show me the text "fail"<o></o>[/FONT]
[FONT=&quot]Screenshot below, and as always thank you so much for any help you can give

[/FONT]

Excel 2010
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2	Data Delivery Status			Data Validation- Persistence						Data Validation - AGS			Final Report - Status
3	%Records outstanding for submission			% Customers Stuck			% TOV's Stuck			%Outstanding			Report Developed			Green	Amber	Red	Country Result	Country Result
4	14			5			5			21			yes			0	24	21	45	45
5	4			1			1			1			yes			7	0	0	7	7
6	18			1			1			15			no			2	33	15	50	35
7	1			15			0			1			no			2	15	15	32	17
8	1			1			0			0			0			2	0	0	2	2
9	87			0			0			0			0			0	0	87	87	87
10	6			0			0			0			0			6	0	0	6	6
Summary

Cell Formulas
Range	Formula
C6	=Jenny!F12
C9	=Jenny!F15
C10	=Jenny!F16
L6	=Jenny!M12
L8	=Jenny!M14
L9	=Jenny!M15
L10	=Jenny!M16
F6	=Jenny!H12
F7	=Jenny!H13
F9	=Jenny!H15
F10	=Jenny!H16
I6	=Jenny!I12
I7	=Jenny!I13
I8	=Jenny!I14
I9	=Jenny!I15
I10	=Jenny!I16
O8	=Jenny!O14
O9	=Jenny!O15
O10	=Jenny!O16
R4	=IF($C4<=11,$C4,0)+IF($F4<=1,$F4,0)+IF($I4<=1,$I4,0)+IF($L4<=1,$L4,0)
R5	=IF($C5<=11,$C5,0)+IF($F5<=1,$F5,0)+IF($I5<=1,$I5,0)+IF($L5<=1,$L5,0)
R6	=IF($C6<=11,$C6,0)+IF($F6<=1,$F6,0)+IF($I6<=1,$I6,0)+IF($L6<=1,$L6,0)
R7	=IF($C7<=11,$C7,0)+IF($F7<=1,$F7,0)+IF($I7<=1,$I7,0)+IF($L7<=1,$L7,0)
R8	=IF($C8<=11,$C8,0)+IF($F8<=1,$F8,0)+IF($I8<=1,$I8,0)+IF($L8<=1,$L8,0)
R9	=IF($C9<=11,$C9,0)+IF($F9<=1,$F9,0)+IF($I9<=1,$I9,0)+IF($L9<=1,$L9,0)
R10	=IF($C10<=11,$C10,0)+IF($F10<=1,$F10,0)+IF($I10<=1,$I10,0)+IF($L10<=1,$L10,0)
S4	=SUM(AND(C4>=12,C4<=19)*C4,AND(F4>=2,F4<=20)*F4,AND(I4>=2,I4<=20)*I4,AND(L4>=2,L4<=20)*L4)
S5	=SUM(AND(C5>=12,C5<=19)*C5,AND(F5>=2,F5<=20)*F5,AND(I5>=2,I5<=20)*I5,AND(L5>=2,L5<=20)*L5)
S6	=SUM(AND(C6>=12,C6<=19)*C6,AND(F6>=2,F6<=20)*F6,AND(I6>=2,I6<=20)*I6,AND(L6>=2,L6<=20)*L6)
S7	=SUM(AND(C7>=12,C7<=19)*C7,AND(F7>=2,F7<=20)*F7,AND(I7>=2,I7<=20)*I7,AND(L7>=2,L7<=20)*L7)
S8	=SUM(AND(C8>=12,C8<=19)*C8,AND(F8>=2,F8<=20)*F8,AND(I8>=2,I8<=20)*I8,AND(L8>=2,L8<=20)*L8)
S9	=SUM(AND(C9>=12,C9<=19)*C9,AND(F9>=2,F9<=20)*F9,AND(I9>=2,I9<=20)*I9,AND(L9>=2,L9<=20)*L9)
S10	=SUM(AND(C10>=12,C10<=19)*C10,AND(F10>=2,F10<=20)*F10,AND(I10>=2,I10<=20)*I10,AND(L10>=2,L10<=20)*L10)
T4	=IF($C4>=29,$C4,0)+IF($F4>=10,$F4,0)+IF($I4>=10,$I4,0)+IF($L4>=10,$L4,0)
T5	=IF($C5>=29,$C5,0)+IF($F5>=10,$F5,0)+IF($I5>=10,$I5,0)+IF($L5>=10,$L5,0)
T6	=IF($C6>=29,$C6,0)+IF($F6>=10,$F6,0)+IF($I6>=10,$I6,0)+IF($L6>=10,$L6,0)
T7	=IF($C7>=29,$C7,0)+IF($F7>=10,$F7,0)+IF($I7>=10,$I7,0)+IF($L7>=10,$L7,0)
T8	=IF($C8>=29,$C8,0)+IF($F8>=10,$F8,0)+IF($I8>=10,$I8,0)+IF($L8>=10,$L8,0)
T9	=IF($C9>=29,$C9,0)+IF($F9>=10,$F9,0)+IF($I9>=10,$I9,0)+IF($L9>=10,$L9,0)
T10	=IF($C10>=29,$C10,0)+IF($F10>=10,$F10,0)+IF($I10>=10,$I10,0)+IF($L10>=10,$L10,0)
U4	=SUM(R4:T4)
U5	=SUM(R5:T5)
U6	=SUM(R6:T6)
U7	=SUM(R7:T7)
U8	=SUM(R8:T8)
U9	=SUM(R9:T9)
U10	=SUM(R10:T10)
V4	=SUM(C4:N4)
V5	=SUM(C5:N5)
V6	=SUM(C6:N6)
V7	=SUM(C7:N7)
V8	=SUM(C8:N8)
V9	=SUM(C9:N9)
V10	=SUM(C10:N10)

[FONT=&quot]<o></o>[/FONT]

 

[gallen]

You mean something like this in U4?:

=IF(O4="No","Fail",SUM(R4:T4))

 

[Conell8383]

@Gallen So simple yet so brilliant. Thank you for your help this works perfectly. Much respect from Dublin