how to find out repeated rows & then adding the values of 1 column to another column?

Originally Posted by kleiber2001

I recommend using something like "=if(COUNTIF($B$1:$C$10,A1)>0,Duplicate,Not Duplicate)" in cell D1 and copying it down the entire lenth of the column. You would have to change B1:C10 to fit the sheet you have of course. Then repete the process in column E with "=if(COUNTIF($C$1:$C$10,B1)>0,Duplicate,Not Duplicate)"

I'm sorry, am totally new to this...would u mind explaining the meaning of the code u wrote..

lets my data is like this
****A**********B********** C
1*** 1********* 21********* 300
2*** 1********* 21********* 400
3*** 2********* 30********* 40
4*** 2********* 30********* 50
5*** 2********* 30********* 60


The answer i expect is in D1 or D2 700 (300+400)
either in D3/D5 = 60+50+40 = 150


PS: * used for spacing nly

These should work for you (in D2)
=IF(A2&B2=A1&B1,"",SUMIFS($C$2:$C$6,$A$2:$A$6,A2,$B$2:$B$6,B2))

or
=IF(A2&B2=A1&B1,"",SUMPRODUCT(--($A$2:$A$6=A2),--($B$2:$B$6=B2),$C$2:$C$6))

Modify the ranges to fit your data.

=IF(A2&B2=A1&B1,"",SUMIFS($C$2:$C$3006,$A$2:$A$3006,A2,$B$2:$B$3006,B2))
Adjust the 3006 to whatever number you need.
Does that work for you?

Originally Posted by ChemistB

=IF(A2&B2=A1&B1,"",SUMIFS($C$2:$C$3006,$A$2:$A$3006,A2,$B$2:$B$3006,B2))
Adjust the 3006 to whatever number you need.
Does that work for you?

Some error came bro..

These are the steps

For the case i mention above (cell values as considered )

For true cdn, it came ""+SUM(A2:C2)

So error came....

But, i want sum of 3 column alone bro... not all the elements frm A2 to C2...

i mean in the abv example for 1 and 21 came same, so true cdn, so i need 300+400 (column c elements)

Okay, my formula will only work if the columns are sorted in order which, based on your picture is not the case. The easiest way to do this is to use a dummy column (Col D). In D2 dragged down, =A2&B2. You can hide this column if you like.

In E2 (dragged down),
=IF(COUNTIF($D$2:D2,D2)>1,"",SUMIFS($C$2:$C$3006,$A$2:$A$3006,A2,$B$2:$B$3006,B2))
Note: Not sure of formatting in India, you might need to replace comma's with semicolons.

That will sum each combination in the first occurance.

=IF(COUNTIF($D$2:D2,D2)>1,"",SUMIFS($C$2:$C$3006,$A$2:$A$3006,A2,$B$2:$B$3006,B2))

COUNTIF($D$2:D2,D2)>1 checks to see if this is the first occurance of the value. If it's a duplicate, it puts a blank, thus insuring that the SUMIFS statement is only used on the first occurance.

SUMIFS($C$2:$C$3006,$A$2:$A$3006,A2,$B$2:$B$3006,B2))
Only available in 2007 and later versions...
$C$2:$C$3006 is the range you want to sum
$A$2:$A$3006 is criteria 1 range
A2 is the criteria 1, in other words, sum all the values between A2 and A3006 if they equal A2
$B$2:$B$3006,B2 2nd criteria range and criteria. a row must meet both criteria (i.e A2 and B2) to be summed.
Hope that helped.