how do i only show contracts that have both positive and negative values associated ?

[zazzz]

Please see table. I have a bunch of contracts and they each row has a dollar value. A contract can appear multiple times in a sheet. Sometimes they'll have a positive value sometimes a negative. I only care about the ones that have both a positive and a negative value (because they'll often match so I can delete them without changing the total).

What formula would I type into Col C?

I'd be grateful for any help!

Contract #	Dollar Amount	Value I Want
ABC	+5	Yes
ABC	-5	Yes
ABC	+5	Yes
DEF	+10	No
DEF	+15	No
GHI	-10	No
GHI	-15	No

[XLent]

one option

C2: =IF($A2=$A1,$C1,IF(SUM(SIGN(COUNTIFS($A$2:$A$8,$A2,$B$2:$B$8,{"<0",">0"}))*{-1,1})=0,"Yes","No"))
copied down

[Glenn Kennedy]

One way:

=IF(AND(COUNTIFS(A:A,A4,B:B,">0")>=1,COUNTIFS(A:A,A4,B:B,"<0")>=1),"Yes","No")

[TMS]

Try:

Please Login or Register  to view this content.

[Bo_Ry]

Another one

=IF(PRODUCT(COUNTIFS(A$2:A$8,A2,B$2:B$8,{"<0",">0"})),"Yes","No")

[zazzz]

Thanks guys! Sorry just seeing this now somehow.

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

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