If Statement color formatting

[Conell8383]

Hi all. I hope you are well. I am trying to work on an If statement and I hope you can help.
What I am trying to achieve is this

IF Cell O4 = "YES" and C4 <11 and F4 is between 0 and 1 and I4 is between 0 and 1, and L4 is between 0 and 1 then Sum C4, F4, I4

I have attached a picture of my issue below for better understanding. I thank you all for an assistance it is greatly appreciated.

 

[Peter_SSs]

I have attached a picture of my issue below for better understanding.

A picture is not a lot of use as we cannot copy the data to our worksheets to test solution.
See Attachments for good ways to provide sample data that can be copied.

In any case, try this for row 4, and copy down.

=IF(AND(O4="yes",MIN(F4,I4,L4)>=0,MAX(F4,I4,L4)<=1),C4+F4+I4,"")

 

[Conell8383]

Hi Peter_SSs Thank you for the help it is greatly appreciated. With =IF(AND(O4="yes",MIN(F4,I4,L4)>=0,MAX(F4,I4,L4)<=1),C4+F4+I4,"") it does not seem to take into account that C4 can be <= to 11. Or have I missed something?

With getting better data up here, I understand that screen shots are not the best way option, Is Dropbox an option??

Again thank you and the community here for all the support that is offered. It really is helping people and their careers.

 

[Peter_SSs]

Hi Peter_SSs Thank you for the help it is greatly appreciated. With =IF(AND(O4="yes",MIN(F4,I4,L4)>=0,MAX(F4,I4,L4)<=1),C4+F4+I4,"") it does not seem to take into account that C4 can be <= to 11. Or have I missed something?

No, it was me who missed that.

=IF(AND(O4="yes",C4<11,MIN(F4,I4,L4)>=0,MAX(F4,I4,L4)<=1),C4+F4+I4,"")

With getting better data up here, I understand that screen shots are not the best way option, Is Dropbox an option??

Screen shots are the best method, but they need to be ones that can be copied from. The link I gave you provides options for that.

Dropbox is an option but many of the experienced helpers here choose not to download such files or are actually prevented from doing so by workplace security settings. The other options will get you many more potential helpers.

 

[Conell8383]

Hi Peter: Thank you again for the support I have downloaded the HTML Maker so hopefully this will make things easier. I have applied your formula but it does not seem to be working for me I have put =IF(AND(O4="yes",C4<11,MIN(F4,I4,L4)>=0,MAX(F4,I4,L4)<=1),C4+F4+I4,"Green") into Cell R4 but even though C4 is 45 it still gives me "green" as an answer. 45 is greater than 11 so it should not be green.
I will now try my new HTML tool to give some understand and insight into my data.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2			Data Delivery Status			Data Validation- Persistence						Data Validation - AGS			Final Report - Status			Result
3	Names	Market	%Records outstanding for submission			% Customers Stuck			% TOV's Stuck			%Outstanding			Report Developed
4	Jenny	Croatia	45			5			2			1			yes			Green
5		Slovenia	4			1			1			1			no			Green
6		Spain	18			1			1			15			0			Green
7		Italy	90			15			0			55			0			Green
8		CORE	100			25			0			0			0			Green
9		MFM	5			0			0			0			0			Green
10		Covance	6			0			0			0			0			Green
Summary

Cell Formulas
Range	Formula
C6	=Jenny!F12
F6	=Jenny!H12
I6	=Jenny!I12
L6	=Jenny!M12
O5	=Jenny!O11
O6	=Jenny!O12
R4	=IF(AND(O4="yes",C4<11,MIN(F4,I4,L4)>=0,MAX(F4,I4,L4)<=1),C4+F4+I4,"Green")
R5	=IF(AND(O5="yes",C5<11,MIN(F5,I5,L5)>=0,MAX(F5,I5,L5)<=1),C5+F5+I5,"Green")
R6	=IF(AND(O6="yes",C6<11,MIN(F6,I6,L6)>=0,MAX(F6,I6,L6)<=1),C6+F6+I6,"Green")

 

[Peter_SSs]

Hi Peter: Thank you again for the support I have downloaded the HTML Maker so hopefully this will make things easier. I have applied your formula but it does not seem to be working for me I have put =IF(AND(O4="yes",C4<11,MIN(F4,I4,L4)>=0,MAX(F4,I4,L4)<=1),C4+F4+I4,"Green") into Cell R4 but even though C4 is 45 it still gives me "green" as an answer. 45 is greater than 11 so it should not be green.

Firstly, my formula had nothing about 'Green', that is something that you added.

In any case, your first post stated:

IF Cell O4 = "YES" and C4 <11 and F4 is between 0 and 1 and I4 is between 0 and 1, and L4 is between 0 and 1 then Sum C4, F4, I4

That is, if all of the blue conditions are met then return the red result. You did not say what result you wanted if any of the blue conditions failed. In the absence of any instructions, my formula said to return an empty string "" if any of the blue conditions failed. You have changed that to "Green". In row 4 of your sample, three of the blue conditions are false (C4 is not less than 11, F4 is not between 0 and 1, I4 is not between 0 and 1) and so "Green" is returned. What result did you expect for row 4 and why?

(The screen shot is good though as it can be copied/pasted for testing )