Conditional formula

[ads_1471]

Hi after a formula for conditional formatting

In column A I have dropdowns where I select an item.
Col B.C,D are amounts
Col E is a final amount

I want it so in Col E the formula is if Col A sees a word then when Col E is greater than 80 it will be green.

If that makes sense

 

[Toadstool]

Hi ads_1471,

Does this do what you want?

ADS_1471.xlsx
	A	B	C	D	E
1	Text1	Val1	Val2	Val3	Total
2	A	3	26	30	59
3	B	31	25	25	81
4	C	29	9	11	49
5	D	22	66	25	113
6	E	20	32	31	83
7		25	55	66	146
8	F	1	22	33	56
9	G	2	21	77	100
10		10	77		87
11	H	30	14	20	64
Sheet1

Cell Formulas
Range	Formula
E2:E11	E2	=SUM(B2:D2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E18	Expression	=AND(A2<>"",$E2>80)	text	NO

 

[ads_1471]

Hi ads_1471,

Does this do what you want?

ADS_1471.xlsx
	A	B	C	D	E
1	Text1	Val1	Val2	Val3	Total
2	A	3	26	30	59
3	B	31	25	25	81
4	C	29	9	11	49
5	D	22	66	25	113
6	E	20	32	31	83
7		25	55	66	146
8	F	1	22	33	56
9	G	2	21	77	100
10		10	77		87
11	H	30	14	20	64
Sheet1

Cell Formulas
Range	Formula
E2:E11	E2	=SUM(B2:D2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E18	Expression	=AND(A2<>"",$E2>80)	text	NO

Yes thanks. Brilliant.

How do you all learn all these formulas I can do basic but struggle with them.

 

[Toadstool]

You're welcome.

If you click Formulas you'll see them all listed by category. You can use the Microsoft site for more details: Excel Functions

The tricky bit with Conditional Format is the debugging as you don't see if it has returned TRUE or FALSE until the OK so I use a work column which you can see if I show more of the sheet.

ADS_1471.xlsx
	A	B	C	D	E	F	G
1	Text1	Val1	Val2	Val3	Total
2	A	3	26	30	59		FALSE
3	B	31	25	25	81		TRUE
4	C	29	9	11	49		FALSE
5	D	22	66	25	113		TRUE
6	E	20	32	31	83		TRUE
7		25	55	66	146		FALSE
8	F	1	22	33	56		FALSE
9	G	2	21	77	100		TRUE
10		10	77		87		FALSE
11	H	30	14	20	64		FALSE
12							FALSE
Sheet1

Cell Formulas
Range	Formula
E2:E11	E2	=SUM(B2:D2)
G2:G12	G2	=AND(A2<>"",$E2>80)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E18	Expression	=AND(A2<>"",$E2>80)	text	NO