Sum Cells Based on Data in Two Columns

cvarela

I need to sum the Cost Per SKU for Transfer ID based on the Uniqueness of the Transfer ID. My brain cannot come up with a way to do this even though I know it is probably possible. What formula do you recommend?

Here is a snapshot of the helper columns I already have and the two columns with data.

ker9

Hi @cvarela

Determine which items are duplicated:

Formula in the Duplicate column: =IF(COUNTIF([Transfer ID]$1:[Transfer ID]@row, =[Transfer ID]@row) > 1, 1, 0)

Formula in the Cost Per Transfer ID column: =IF(Duplicate@row = 0, SUMIFS([Cost Per SKU for Transfer ID]:[Cost Per SKU for Transfer ID], [Transfer ID]:[Transfer ID], =[Transfer ID]@row), "-")

You can make the Duplicate column a Checkbox and it will show checked for 1 and blank for 0.

Hope this helps!

ker9

Hi @cvarela

Determine which items are duplicated:

Formula in the Duplicate column: =IF(COUNTIF([Transfer ID]$1:[Transfer ID]@row, =[Transfer ID]@row) > 1, 1, 0)

Formula in the Cost Per Transfer ID column: =IF(Duplicate@row = 0, SUMIFS([Cost Per SKU for Transfer ID]:[Cost Per SKU for Transfer ID], [Transfer ID]:[Transfer ID], =[Transfer ID]@row), "-")

You can make the Duplicate column a Checkbox and it will show checked for 1 and blank for 0.

Hope this helps!

cvarela

@ker9 That is exactly what I was looking for! Thank you so much for teaching me!