highlight a category based on criteria

[Ash Ford]

Hi all,

i have been trying for days to filter a table based on criteria either by formula, conditional formatting or both.
please find attached a spreadsheet including the data and what i would like the result to be.
1. if a cell in column C has a negative number find the corresponding category in column B - there may be multiple rows
2. highlight all of all of the cells in the category (column B) if the corresponding cell in column C is less than or greater to 0 (do not include cells that are blank or contain 0)



many thanks,

[JohnTopley]

For 2.

CF

=AND($C5<>"",$C5<>0)

For 1. Do you want a list of all categories where c is negative?

Column V does not meet that criteria (based om comment in V27

[Ash Ford]

Hi John,

columns B and C are the only columns where i need a solution - sorry but column V is only what i wanted the formula to achieve.

i'm struggling to explain what i'm after ....
if column C = a negative number - in this case A1, B1 etc
now highlight all A1's in column B if the number is not equal to 0 or blank

the result i would like is column M - highlight column M if N contains numbers not equal to 0 or blank .... but only if there is a negative number. (D1 and E1 not highlight because they don't contain a negative)

many thanks

[JohnTopley]

Try


=AND($C5<>"",$C5<>0,COUNTIFS($C$5:$C$1000,"<0",$B$5:$B$1000,$B5))

[Ash Ford]

Hi John,

Apologies for the delayed reply but that formula worked perfectly - thank you very much!!
Problem solved

Many thanks

[JohnTopley]

No problem.



If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.