How can I sum amounts only once from duplicate texts?

Lee Unger

I'm trying to figure out a way to sum the amount column using only distinct clients and amounts that are tied to duplicates once.

for the sake of the example let's say this is my data, the actual file has thousands of rows:

in this instance, I want to create a formula that will sum 200 (for FIFA) + 100 (for Mcdonald’s) + 400 for Coca-cola only once. So essentially, check for duplicates in the "Client column", and if the row has a duplicate there, sum the number from the amount column only once.

I tried using the SUMIF + DISTINCT but couldn't get it to work. I created a helper column using IF to show me when a cell is a duplicate by marking it with "1", but that doesn't help me add the first instance of the amount to the formula.

Any ideas here?

Thanks in advance!

Paul Newcome

You are on the right track with the helper column, but you are going to need 2 of them. One would be an auto-number column with no special formatting (called "auto" in this example) and the other would be your checkbox. Use this column formula to check off rows for the first entry of each unique client:

=IF(COUNTIFS(Client:Client, @cell = Client@row, Auto:Auto, @cell<= Auto@row) = 1, 1)

Then to get your totals you would do a SUMIFS incorporating the checkbox:

=SUMIFS(Amount:Amount, Checkbox:Checkbox, @cell = 1)

Paul Newcome

You are on the right track with the helper column, but you are going to need 2 of them. One would be an auto-number column with no special formatting (called "auto" in this example) and the other would be your checkbox. Use this column formula to check off rows for the first entry of each unique client:

=IF(COUNTIFS(Client:Client, @cell = Client@row, Auto:Auto, @cell<= Auto@row) = 1, 1)

Then to get your totals you would do a SUMIFS incorporating the checkbox:

=SUMIFS(Amount:Amount, Checkbox:Checkbox, @cell = 1)

Lee Unger

Worked perfectly thanks! I just left the auto-number column blank correct?

Paul Newcome

Happy to help. 👍️

And yes. Leave all of the column properties and whatnot blank so that it populates with a number. A prefix, suffix, or leading zeros will create text strings which cannot be evaluated by the "less than or equal to" portion of the formula.