Sum calculation seems incorrect in connected cells

Question

I have a table where I added a table filter to do sum calculations automatically. We work with merged cells, since one PO can be linked to several invoices.

Somehow the calculation doesn't calculate the merged cells properly, instead it seems to add it up for every line. I think the picture shows the problem:

The proper sum should be 31.000 but it seems to multiply this number by 3, since there are 3 merged cells?!

Any hints how to fix this?

Matthias Goepfert · Answer

Hey Stiltsoft support

Thanks for your reply.
To be honest I don't see the reason why you would want to merge cells when you expext them to sum up in every row.
But probably that is an academic discussion, or one of different usecases.

Stiltsoft support · Answer

Hi @Matthias Goepfert ,

Such behavior is expected and correct one for the Table Filter macro. Please check the screenshot below:

The sum for merged cells is not right for the Vertical Totals, but if you count Horizontal Totals the result is correct.

If "31,000" is recognized not in all the cells that were merged but only in the first one, we won't get the "32,000" for our Horizontal Totals. The result will be "32,000", "1,000" and "1,000". And the more merged cells we use, the more mistakes we get.

So, if you can't change the structure of your table, you may use the Table Spreadsheet macro and its cell formulas:

Hope it helps your case.