Conditional formatting

[Stclements1]

I am trying to write a new rule in conditional formatting but am having problems. In column S starting with cell S2 through to S30 I want to format the spreadsheet so that if the value in any of the S column is >0 then the corresponding row should be formatted with a yellow fill. Then I want the value in cell M35 to be the sum of all the values in column M that are not filled. I:e there is no value in the corresponding cell in column S

 

[Peter_SSs]

It isn't clear what sort of data you have in column S. For the future it would assist your helpers considerably if you would consider using XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

Anyway, see if any of this helps.

23 11 04.xlsm
	A	B	C									L	M	N	O	P	Q	R	S
1
2													1						6
3													2						2
4													3						-3
5													4
6													1						5
7													2						-2
8													3						1
9													4
10													1
11													2
12													3
13													4
14
15
28
29
30
31
32
33
34
35													23
CF

Cell Formulas
Range	Formula
M35	M35	=SUM(M2:M30)-SUMIF(S2:S29,">0",M2:M30)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:S30	Expression	=$S2>0	text	NO

 

[Stclements1]

Many thanks for your explanation although I tried it and it didn't seem to work for me. I have attached the spreadsheet to give you greater clarity. I tried to use the XL2BB but I kept receiving a message saying "the file type is not supported in protected view", which I didn't know how to fix. In cell M48 I am trying create a formula that gives me the sum of all outstanding values in column M that have not been paid based on the corresponding value in column S. If the corresponding value in column S is 0 then its value in column M will be added to the value in cell M48 and if the corresponding value in column S is greater than 0 then no value is added from the corresponding cell in column M to the value in cell M48. Then when the value in column S is greater than 0 then the row should be conditionally formatted to have a yellow fill. I tried to upload the spreadsheet to assist you but I was unable to do so.

 

[Peter_SSs]

message saying "the file type is not supported in protected view"

Review the first bullet point in the "Known XL2BB Issues" near the top of the XL2BB Instructions & Download page.

Sounds like your data may be more like this

23 11 04.xlsm
	A	B	C									L	M	N	O	P	Q	R	S
1
2													1						1
3													2						2
4													3						0
5													4						0
6													1						1
7													2						0
8													3						3
9													4						0
10													1						0
11													2						0
12													3						0
13													4						0
14
15
28
29
30
31
32
33
34
35													23
CF

Cell Formulas
Range	Formula
M35	M35	=SUMIF(S2:S29,0,M2:M30)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:S30	Expression	=$S2>0	text	NO

 

[Stclements1]

My apologies, the formula does work the reason it wasn't previously is that in Column S I have the code =if(T2>0,"1","") and with the code in the cell the formula you gave me wouldn't not work, once I removed the formula from column S your formula worked. The problem I have is I wanted S column to have a 1 generated every time there was a value in the corresponding cell in column T and now it would seem I have to do this by hand, unless you know where I am going wrong. I get the impression that the formula in cell M35 wont read any value in column S whilst there is an underlying formula in there.

 

[awoohaw]

My apologies, the formula does work the reason it wasn't previously is that in Column S I have the code =if(T2>0,"1","") and with the code in the cell the formula you gave me wouldn't not work, once I removed the formula from column S your formula worked. The problem I have is I wanted S column to have a 1 generated every time there was a value in the corresponding cell in column T and now it would seem I have to do this by hand, unless you know where I am going wrong. I get the impression that the formula in cell M35 wont read any value in column S whilst there is an underlying formula in there.

Try these updates then:

Mr Excel Questions 71.xlsm
													M						S	T	U	V	W	X	Y	Z
1
2													5						1	6
3													3						1	6
4													5						1	6
5													6
6													4
7													3
8													3
9													6
10													4
11													4
12													6						1	4
13													6						1	3
14													3						1	4
15													4						1	6
16													6						1	6
17													4
18													6
19													3
20													4
21													5						1	6
22													6						1	4
23													6						1	5
24													5						1	3
25													6						1	4
26													4
27													6
28													6						1	6
29													6						1	3
30													6						1	3
31
32
33
34
35													84
StClements1

Cell Formulas
Range	Formula
S2:S30	S2	=IF(T2>0,1,"")
M35	M35	=SUMIF($S$2:$S$30,">0",$M$2:$M$30)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M2:Z30	Expression	=AND($S2>0,$S2<>"")	text	NO

 

[Stclements1]

Thank you all so much it is all working perfectly now.
Many thanks.

 

[Peter_SSs]

Thank you all so much it is all working perfectly now.
Many thanks.

You're welcome. Glad we could help.

I have removed the solution mark from post #7. Please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers.

the reason it wasn't previously is that in Column S I have the code =if(T2>0,"1","")

Remove the quotes from around the 1, after all it should be a number, not a text value

=if(T2>0,1,"")