Formula to sum differences of duplicate records

[vindictive666]

Hi,

I have this attached file where I need to sum the difference of duplicate records for each person. For each item in col. B, I need to calculate the difference of elements in col. C with the values in col. D.
I did this with a pivot table by putting one column show difference from... but I still need to do a conditional formatting and a SUMIF.
Maybe there is a formula which can sum all these with no need to perform additional steps?

Thank you.

[AliGW]

I don't see any expected results mocked up in your workbook. Please add these to show what we are aiming for.

[jason.b75]

Wild guess

Please Login or Register  to view this content.

[vindictive666]

Hi, sorry for that, I have attached again.

[vindictive666]

Hi Jason. Thanks for this, but the result needs to be 128.

[jason.b75]

I thought you wanted a formula to show difference per 'Marca' (guessing Marca = Marker / I.D. or similar). Getting all of those individual results into one formula then isolating the negatives takes a bit more. See if this works, it gives the same result of -128 in the sample, so that is a good start

Please Login or Register  to view this content.

If you want the result displayed as positive then you just need to remove the - symbol in front of the middle 0 at the end of the formula.

[vindictive666]

Hi Jason, that is working. That's a tough one. Can you please explain the logic behind this formula?

[jason.b75]

Start with a simpler version of the formula, enter this into E2 of your sample file, then fill down.

Please Login or Register  to view this content.

You will see that this gives the difference of each row in the same way as your pivot table.

To condense all of this to a single formula, the criteria B2 needs to be expanded to the full range, using B:B is not a good idea, it will be slow to calculate and empty rows can mess things up, which is why I set the formula to the rows holding the data.

When SUMIFS (or any similar function) has more than one item in a single criteria (an array) then the array results need SUMPRODUCT (or SUM, the correct one to use depends on the criteria) to bind them together. This would give the overall total from the positive and negative results.

As you only want the total of the negative results, TEXT is used to apply a custom number format which removes positive values from the array before they are totalled by sumproduct. The string at the end, "\0;-0;0" defines the format. Google can explain excel custom number formats better than I can

[vindictive666]

Thanks a lot for your patience and support!

[vindictive666]

Hello Jason,

I discovered that if Zile CM Online is different than Zile CM pontaj, (i.e: Zile CM Online = 40 and Zile CM pontaj = 32), the formula doubles the result for the specific IDs where this happens, so instead of 8 (40 - 32), it adds 16 to the final result.
How is this possible?

[jason.b75]

I'm not seeing it with the sample file.

Is this happening where there are 2 or more rows with the same Marca and Tip Pontaj?

I think that in such situations, the values against each row will be totalled and duplicated (or triplicated with 3 identical rows, etc.)

If you can provide a sample file with a data set that highlights the problem then I will be able to identify the cause easier. If it is multiple identical rows causing it, then i might not be possible to do this in a single formula.

I'm not giving up that easily though, just need more to work with

[vindictive666]

You're not seeing it because I applied the formula to another sheet and then I figured it out Please see attached now. However, I think this is far easier with a calculated field in a Pivot Table, right?

[jason.b75]

I rarely use pivottables, so basic data summaries are my limit, you most likely know more about caculated fields than I do

I've had a look at the formula with the new sample file and found where it was going wrong, with the way I had set up the formula it was calculating the difference against both Online and Pontaj, then adding them together. Using Sum/Count fixes that problem, hopefully it doesn't create any new problems that I haven't seen

Please Login or Register  to view this content.

[vindictive666]

I rarely use pivottables, so basic data summaries are my limit, you most likely know more about caculated fields than I do

How can I know more? The formulas you proposed are so intricate
Thanks a lot for taking your time for this small issue. So far it's working and will let you know if any issues occur.

[AliGW]

Maybe here?

https://www.contextures.com/excel-pi...ted-field.html

[vindictive666]

Thank you Ali, this also works.

@jason.b75, why did you remove the -- on the second formula?

[jason.b75]

Sumproduct doesn't recognise anything returned by TEXT() as numeric, so -- is used to coerce the text values back to numeric format, in the second formula the division that was added to it serves the same purpose, so -- is no longer needed.

Using your second sample file to demonstrate, you have entered the formula, =IF(AND(C2=C3,D2<>D3,E2>E3),"1","0") into F2, filled down to F106.

Try using =SUM(F2:F106) and you will see that the result is 0 because "1" and "0" are text, not numbers.
Try it with =SUMPRODUCT(--F2:F106) and it will give you the total that you expect because -- converts the text to numeric. Any mathematical operation will work, -- is typically used when there is no other operation required.

Depending on who writes the formula, you might see --, +0 or *1, all of which serve the same purpose.

Also, writing your formula as =IF(AND(C2=C3,D2<>D3,E2>E3),1,0) is better practice, entering numbers into a formula in numeric format eleiminates the need to convert text back to numeric afterwards.

With most things excel, there will always be exceptions to the rule.