Formula problems

Jerminator

New Member
Joined
Aug 8, 2002
Messages
33
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 :biggrin:
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,192
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 3, 2002
Messages
3,962
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?
 

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962

ADVERTISEMENT

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,192
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 8, 2002
Messages
33

ADVERTISEMENT

oops, good question :) Actually the larger number.
 

shades

Well-known Member
Joined
Mar 20, 2002
Messages
1,550
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,192
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,192
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,148,528
Messages
5,747,221
Members
424,069
Latest member
kamkwok1hh

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top