Formula problems

[Jerminator]

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

 

[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]

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]

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

 

[phantom1975]

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]

oops, good question Actually the larger number.

 

[shades]

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.