# Formula problems

#### Jerminator

##### New Member
OK, just a thanks up front if anyone can help me. I have a monthly report to generate. I need to calculate a colum in this report, a simple sum of entire column. My problem is I need to subtract from that total any duplicates based on another column. Example: Column A: Name Column B: Dollar Amount. =SUM(B:B) But in the Name column I dont want to sum the dollar amount of repeated names. Thanks again

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### Joe4

Here's one way.

In another column (i.e. column C), enter a formula to check for duplicates.

Let's say that you have data in A1:B100. In C1, enter this formula and copy down to row 100
=IF(A1=A2,0,1)

This will put a 1 next to values we want to add (if there are duplicates, only the last one in the list will have a "1" next to it).

Now we can use the SUMIF formula, i.e.
=SUMIF(C1:C100,1,B1:B100)

#### phantom1975

##### MrExcel MVP
So if a name is duplicated in column A, you don't want to sum ANYTHING with that name or you only want the first entry and ignore subsequent entries or you want to ignore identical entries?

#### Jerminator

##### New Member
I only want to sum the duplicate name once in my final dollar computation. Thanks

#### phantom1975

##### MrExcel MVP
Which entry though? The first entry? The one with the greatest dollar amount? The last entry?

#### Joe4

I was under the assumption that the duplicate records also had duplicate amounts, so it wouldn't matter which one was chosen, but maybe I am assuming too much...

If my assumtpion is correct, my solution will work.

#### Jerminator

##### New Member
oops, good question Actually the larger number.

##### Well-known Member
jmiskey said:
Here's one way.

In another column (i.e. column C), enter a formula to check for duplicates.

Let's say that you have data in A1:B100. In C1, enter this formula and copy down to row 100
=IF(A1=A2,0,1)

This will put a 1 next to values we want to add (if there are duplicates, only the last one in the list will have a "1" next to it).

Just out of curiosity, doesn't this formula only work if the duplicates are all adjacent to each other? If they are sprinkled throughout, then another approach will have to be used.

#### Joe4

Just out of curiosity, doesn't this formula only work if the duplicates are all adjacent to each other? If they are sprinkled throughout, then another approach will have to be used.
Yes. If they weren't next to each other, you would want to sort the list first.

There is another variation you could write using the COUNT function that doesn't require the records being next to each other or sorting, though it would be a little more complicated.

#### Joe4

oops, good question Actually the larger number.
If you sorted the list, first on column A then on column B and then applied my solution, it should work because then the largest value would always be the last entry.

Replies
5
Views
666
Replies
6
Views
2K
Replies
8
Views
3K
Replies
3
Views
1K
Replies
3
Views
151

1,181,786
Messages
5,932,041
Members
436,816
Latest member
Composh

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back