i'd like to put in a formula that shows whether certain cells are relevant or not

[bigdan]

Hello all.

Please see sample data below. This is cols A & B. In Col C I'd like to paste a formula which tells me if this contract is relevant or not.

A contract is relevant if it has both a positive amount and a negative amount associated with it. So Contract ABC is relevant because it has both a +5 and a -6 associated with it. The other 2 are not because they have either all negs or all positives.

Btw contracts could appear as many as 50 times. I only showed 2 instances here as an example.

This is the formula I have so far. It almost works but not exactly. With this formula it would show the first row with Contract ABC as not relevant and the second one as relevant. I need both to show up as relevant.

=IF(AND(COUNTIFS($A:$A,$A2,$b:$b,"<0"),COUNTIFS($A:$A,$A2,$b:$b,">0")),"Yes","No")

Would appreciate any help!

Contract Amt ABC 5​ DEF -3​ DEF -4​ GHI 7​ ABC -6​

 

[Fluff]

That formula works fine for me

+Fluff 1.xlsm
	A	B	C
1	Contract	Amt
2	ABC	5	Yes
3	DEF	-3	No
4	DEF	-4	No
5	GHI	7	No
6	ABC	-6	Yes
Main

Cell Formulas
Range	Formula
C2:C6	C2	=IF(AND(COUNTIFS($A:$A,$A2,$B:$B,"<0"),COUNTIFS($A:$A,$A2,$B:$B,">0")),"Yes","No")

Is col B a formula or hard values?

 

[jasonb75]

Untested, try

=IF(COUNTIFS(A:A,A2,B:B,IF(B2>0,"<0",">0")),"Yes","No")

 

[bigdan]

You're right! It does work properly in the dataset I attached. Not sure why, since in the working file it doesn't. Here's a screenshot.

As you can see here, the first time the contract is mentioned it's considered not relevant and it's only in the second row onwards that it's considered relevant.

 

[bigdan]

Untested, try

=IF(COUNTIFS(A:A,A2,B:B,IF(B2>0,"<0",">0")),"Yes","No")

Works! Thanks very much

Edit - I'm not finding the 'mark as solution' button next to your post soooo not sure what to do.
Edit 2 - nevermind, i see it on the far far right. thanks for your help!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[jasonb75]

Maybe @Fluff can see something that I don't, but I'm failing to see why one would work but not the other. It might be a good idea to look into it a little more in case it is returning the correct results by fluke more than accuracy.

 

[Fluff]

Maybe @Fluff can see something that I don't

Nope.

It might be a good idea to look into it a little more in case it is returning the correct results by fluke more than accuracy.

Agreed.