Sum & Drag formula on merged cells

[Pierre]

Hello everyone,

I have a list of customer that are in merged cells, next to it there is a quantity (not merged cells). I would like to sum the quantity for each customer in a merged cell as well (please see file enclosed). There is two issues: first to make the sum, and then to drag the formula on merged cells.
Any ideas on how to solve this?
Thank you!

 

[Khalid NGO]

Hi Pierre,
Welcome to the forum...

I've had the similar problem, and found nothing, except the universal solution:
"do not merge cells"

Regards,

 

[Somendra Misra]

Hi Pierre,

Merged cells should be avoided refer below links for the reasons mentioned by @bobhc

http://chandoo.org/forum/threads/count-function-in-merged-cells.18394/#post-111089

But in case you want to see the formula working here are some steps need to be followed to work with your file.

1. Put any character just after the last row of table in customer column, I had put 1.
2. Un-merge sum column and put the formula in C2 and copy down. Note the formulas are array formulas so must be entered with Ctrl+Shift+Enter.
3. Select Cells with common customer of sum column and merge them.

See the file for illustration.

Regards,

 

[Pierre]

Thank you Somendra for you answer, this is a nice looking formula! I will have to spend some time to study and understand it

I guess I can do as you suggest, i.e apply formula on non-merged cell and then apply the merging. I found that I can simply copy the format from the "Customer" column and apply it to the "Sum" column.

May I go further and ask what would be the vba code to do automatize it: a code that would select the cells in the "Customer" column until the last non empty cell, copy the format and apply it to the "Sum" column?