Conditional Formatting

[Cooki]

Hi All

Hard to explain but ill give it ago

Column A is activity and column B is Percentage

Column A can have the same tasks but for different departments e.g Relocation - HH or Relocation - NHH

But column A can have individual tasks.

What i need is, if the tasks percentage is NOT 100% then highlight, but if the same task just by different departments adds up to 100% or more between them then don't highlight, if it does not don't highlight

Relocation Jobsheets - HH.	80.00%​	Don’t Highlight - as together they are 100%
Relocation Jobsheets - NHH.	20.00%​	Don’t Highlight - as together they are 100%
Install - HH.	90.00%​	Highlight
Data - NHH	100.00%​	Don’t Highlight
Flows - HH	20.00%​	Highlight - as together they are 40%
Flows - NHH	20.00%​	Highlight - as together they are 40%

I have made this, but it just adds up the tasks that are simulate which is not right, but im sure its something along these lines, just cant figure it out.

=SUMIF($A$1:$A$6, LEFT(A1, FIND("-", A1, 1)) & "*", $B$1:$B$6)

Hope that makes sense

Thanks in advance if you can figure it out or at least try

 

[jasonb75]

Soooo close

=SUMIF($A$1:$A$6,LEFT(A1,FIND("-",A1,1))&"*",$B$1:$B$6)<1

 

[Cooki]

Soooo close

=SUMIF($A$1:$A$6,LEFT(A1,FIND("-",A1,1))&"*",$B$1:$B$6)<1

Thanks for the response, but the forumla will not do what i need. I dont think its correct for what i need.

Does not check if a cell is 100% or if two cells with same task adds up to 100% and the rest of it.

 

[jasonb75]

but the forumla will not do what i need. I dont think its correct for what i need.

It will do what you need and it is correct.

Book1
	A	B	C
1	Relocation Jobsheets - HH.	80.00%	Don’t Highlight - as together they are 100%
2	Relocation Jobsheets - NHH.	20.00%	Don’t Highlight - as together they are 100%
3	Install - HH.	90.00%	Highlight
4	Data - NHH	100.00%	Don’t Highlight
5	Flows - HH	20.00%	Highlight - as together they are 40%
6	Flows - NHH	20.00%	Highlight - as together they are 40%
Sheet8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A6	Expression	=SUMIF($A$1:$A$6,LEFT(A1,FIND("-",A1,1))&"*",$B$1:$B$6)<1	text	NO

 

[Cooki]

It will do what you need and it is correct.

Book1
	A	B	C
1	Relocation Jobsheets - HH.	80.00%	Don’t Highlight - as together they are 100%
2	Relocation Jobsheets - NHH.	20.00%	Don’t Highlight - as together they are 100%
3	Install - HH.	90.00%	Highlight
4	Data - NHH	100.00%	Don’t Highlight
5	Flows - HH	20.00%	Highlight - as together they are 40%
6	Flows - NHH	20.00%	Highlight - as together they are 40%
Sheet8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A6	Expression	=SUMIF($A$1:$A$6,LEFT(A1,FIND("-",A1,1))&"*",$B$1:$B$6)<1	text	NO

Wow apologies it was me, had the table in different cells.

Really do apologies.

Dam cant believe how close i was

Thanks for the help