Summing unique values in rows with duplicates

Hello, I hope this is the right location for this question. If not, please re-direct.

Attached is a modified sample of my data, which you'll find in the Sample tab. I am looking for a cell function that would be able to sum a range of values in the 'Diff Weight' rows. The function should include negatives and exclude #VALUE!. I would like to be able to modify the function whenever new columns are added to the range, and quickly repopulate the Total column. Whenever a new column (item) is added, previous data points (rows) are populated with NA and #VALUE! (e.g. Bananas), and the function should be able to work with that.

I have found a function that sums unique values with duplicates in a column, and you'll find this example in the second tab, Summing Duplicates. But I get an incorrect sum when attempting to use it for a row. Does the FREQUENCY or IF functions not work for rows, or might it be another issue?

I have also been trying to create a pivot table that might re-organize my data and put 'Diff Weight' into columns instead, but have not been successful and my knowledge of pivot tables is more limited. This might also be another avenue to explore.

Something like this?
//Ola

Dear osala,

Not quite. I think you did a grand total of the Diff Weight in the True Totals column. The True Totals column is there as a reference for what the correct sum should be for that row of Diff Weight. I would like to find a function that achieves that sum to be placed in the yellow highlighted cells directly left of the True Totals column. I'm sorry if that was unclear before.

Something like this?
//Ola


1. I used a regular row sum (excl. error values) since I did not understand the logic behind the irregular row sum (where certain non error values were excluded).
2. I used conditional formatting (white font color) to hide unwanted sums, and another to paint a yellow background.
The formula is an array formula (confirm with ctrl+shift+enter) that can be copied down.

Hi Ola,

That looks fantastic! There's just one more issue that needed to be addressed. There are duplicate values within each Diff Weight row that needs to be counted only once, so you'll notice that the generated Total for [60612 M1HS] and [60612 L1WW] are different from the true totals. I had mentioned this in the title but should have emphasized the point. Thank you for your help.

Here is an exact match to the True Total.
//Ola


To simplify the formula I used a VBA script:
http://www.cpearson.com/excel/distinctvalues.aspx


When you are happy with the solution, please mark the Thread as Solved under the Yellow bar, top right, under Thread Tools.

Thank you, Osa, the function works perfectly. I'm sorry for the delayed response. I was gone for a few days, and it took a little while to figure out how to implement.