SUMIF duplicate

**magman1984** · 02-10-2011, 08:10 AM

Hi

I have two columns with hard data.

Column A
1234
1234
1234
5678
5678
9876
9876
9876
9876

Column B
10
10
-20
30
-30
100
100
100
-300

Column C (Here I want my formula)

I want to sum the values in column B which belongs to the data in column A. Example: Sum 10+10-20 because the value in A1, A2 and A3 are the same.

If no duplicate is found in column A; then I just want the connetcted value in column B as a result.

**Colin Legg** · 02-10-2011, 08:13 AM

If I understand correctly, you can use SUMIF() to do this:

=SUMIF($A$1:$A$9,$A1,$B$1:$B$9)

copied down

**magman1984** · 02-10-2011, 08:44 AM

Thanks.

What if I have a third column C with hard data and want both column A and C as a argument?

Column A
1234
1234
1234
5678
5678
9876
9876
9876
9876

Column B
10
10
-20
30
-30
100
100
100
-300

Column C
A
A
A
B
B
C
C
C
C

Only sum if column A got duplicates (first criteria), and column C (second criteria).
F.eks.
Sum data from column B if $A$1:$A$9 got duplicates, but only if $C$1:$C$9 also duplicates.

**Colin Legg** · 02-10-2011, 10:16 AM

There are a couple of ways to do this. Since you are using Excel 2003 and there could be many rows of data, I think a good approach for you would be to use a helper column. In a spare column you can concatenate the values in columns A and C (separating them with an unusual character to prevent an unlikely miscalculation) and then to use that column as the criteria for summing.

For example, with your data in A1:C9, in cell E1 you could put this formula and fill down to E9:
=A1&"-"&C1

Then, to get your totals (say in column G), put in this formula and fill down:
=SUMIF($E$1:$E$9,E1,$B$1:$B$9)

Hope that helps...

**magman1984** · 02-10-2011, 10:33 AM

Originally Posted by Colin Legg

Since you are using Excel 2003

Sorry, I have to change that. I got 2007 version on this computer.

I will try to do it the "Excel 2003 way", but it's maybe possible to do it without a helper column in version 2007?

**Colin Legg** · 02-10-2011, 10:37 AM

Yes, in Excel 2007 you can use the SUMIFS() worksheet function.

=SUMIFS($B$1:$B$9,$A$1:$A$9,A1,$C$1:$C$9,C1)

It is actually very possible to do this without a helper column in Excel 2003, but the formula is inefficient and would be overly complicated for a new user, hence why I recommended the helper column.

**magman1984** · 02-10-2011, 11:54 AM

Thank you, again!

This saves me a lot of time.

**paperwings25** · 08-31-2011, 10:12 AM

This is excellent help. If I could just build on this question with a question of my own.

I have implemented the SUMIF function, but I want the formula to subtract out the # in column K IF column M has the words "2010 End Bal". The columns I am using are Column A, Column K, and Column M. Column A has all my policy numbers. Column K has my Journal Transaction Amounts. Column M has "end balance 2009" and "2010 End Bal".

My current formula is =SUMIF($A$2:$A$56074,$A2,$K$2:$K$56074). It works half way b/c it is including the "2010 End bal" transaction totals that I do not want included b/c I am comparing the end balance 2009 + transactions throughout the year to the 2010 End Bal.

Any help is greatly appreciated!!

**Miraun** · 08-31-2011, 10:16 AM

Nope. Hijacking others' threads to ask additional questions is against the forum rules. I'm sure one of the admins will do one of their copy/paste mean messages. You can always start a new thread referencing this one though... I've definitely been guilty of that a couple times...

And I replied to your other thread regarding the data.

**NBVC** · 08-31-2011, 10:18 AM

To confirm:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.