# Highlight positive and negative numbers based on data from another column

1. ## Highlight positive and negative numbers based on data from another column

Abc 500
abc -500
bbb 300
bbb 250
cac 100
cac -100
ddd 200
ddd 300
fff -500

I want to be able to highlight data in column B where amount is identical (ignore negative sign) only where data in column A is identical.

In this particular case it should highlight 500,-500 where data in column A = ABC & also 100,-100 where data in column A =CAC.  Register To Reply

2. ## Re: Highlight positive and negative numbers based on data from another column

Hi,

Assuming your data is in A1:B9, enter the following in C1

=SUMPRODUCT((\$A\$1:\$A\$9=A1)*(\$B\$1:\$B\$9=-B1))

and copy down to C9

A result of 1 indicates a match.  Register To Reply

3. ## Re: Highlight positive and negative numbers based on data from another column

id go for
=SUMPRODUCT((\$A\$1:\$A\$9=\$A1)*(ABS(\$B\$1:\$B\$9)=ABS(\$B1)))>1
which you can use as a formula rule in conditional formatting  Register To Reply