How can I sum amounts only once from duplicate texts?

Options

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!

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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)

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!