COUNTIF formula - help adding more to it

[jamstr]

Hi there

I have a spreadsheet (12000+ rows). Column A has lots of names but essentially only about 20 different ones.
Column B has values, many positive and negative numbers, that cancel themselves out.

I want to delete the rows that cancel themselves out BUT only if the text in Column A is the same. I can do the first part but cannot expand on the formula.
If I filter on A to only show one name the following formula works. If I expand it to be all rows then the answer is wrong.
=COUNTIF($B$2:$B$100,B2)<>COUNTIF($B$2:$B$100,-B2)

Example 1:

Apple	12.46​	TRUE
Apple	3.45​	FALSE
Apple	-3.45​	FALSE

<tbody>
</tbody>

Example 2:

Apple	12.46​	FALSE
Apple	3.45	FALSE
Apple	-3.45	FALSE
Pear	8.20	TRUE
Pear	-12.46	FALSE
Bananas	15.22	TRUE

<tbody>
</tbody>

Can I have a formula that works like example 1 but checks to see if the text in A is the same?
=COUNTIF($B$2:$B$100,B2)<>COUNTIF($B$2:$B$100,-B2) only if A = A

Thanks
Stef

 

[FormR]

Hi, welcome to the forum!

Maybe you could use COUNTIFS() to also test that column A matches:

=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)<>COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,-B2)