# Deleting Rows with coinciding positive and negative values

new here and I apologize if I don't follow the correct etiquette, nonetheless, I wanted to know if there is a way to write a formula which can delete rows if it meets certain criteria and it has corresponding positive and negative values, for example, say we have the following three values columns

``````2856467        99745        1
2856467        99745       -1
4556434        87452       2
1856434        78549       7

``````

The formula I thought would be, =IF(A1=A2,IF(B1=B2,C1+C2,""),""), which I know is incorrect, but it was my first attempt. Essentially, what would make this better would be to have it check al four entries on column A, and then once it finds a match have it check all four entries in B, and once again when it finds a match then if the values in C coincide but are inverses of each other, i.e. one positive-one negative then these two would be deleted. If these conditions were not met we would leave them alone. Would this be something that would be possible to do? Thank you all help is greatly appreciated, as I am still a beginner in Excel.

• You mean to say if column A have duplicate then check for duplicates in column B,, and if has duplicate then in column C ,, and delete on positive & one negative. But what about if no duplicates in col A & B,,, or may one has,,, please edit your post and be clear and loud about your needs,,, and avoid votes to close the question !! Commented Nov 6, 2020 at 4:26

Flag as TRUE if the sum of the value column by the ID column is zero.

``````=SUMIF([id],[@id],[a number])=0
``````

Then delete the rows with TRUE next to them. Or just ignore them in your other calculations.

EDIT:

To check multiple columns, use SUMIFS:

``````=SUMIFS([value],[id],[@id],[sub_id],[@[sub_id]])=0
``````

• This doesn't quite seem to work when I try doing it, and from what I can see, it doesn't really take into consideration what was in column B, i.e. "something else", as it should. Since, A2 & A3 coincided it should've then looked at B2 and B3, after which where it saw that the values where the same but with different signs, it would then identify them as needed to be deleted. But if B2 does not equal B3, then it would've had ended right there. I attempted this and it returned all FALSE statements. Commented Nov 5, 2020 at 20:21
• OK. Your post was not clear. Please see my edit. Commented Nov 5, 2020 at 20:34