Sum Values Based on Criteria

Hello Team,

Need your expert help in creating a macro to the below sum up the values with below criteria.

I have set of data which I work on monthly basis. In my scenario there are a list of customers and I need to sum up amount due from customer for the month. However there are a few customers with head office and branches and I want to see the total amount due (roll up amount due from branches with head office of a given customer) under the head office irrespective of the number of branches they have.

My data : Sheet 1: Column A has a unique customer ID which is generated irrespective of head office or branch. Column B has the Customer's legal name & Column C has amount due.

Sheet 2: Info with regards to Main office and branches of each customer

Sheet 3: Should have the total amount due for each individual customer (should have individuals / main office only and cannot have branches.

I have attached my test sheet for your reference.

Thanks a lot in advance for your help

Regards
mohdshahtab

Thanks PCI for your response.

Could we say that the Company name is always the first word in the "Cust Name"?
So the key to search information will be this word
Unfortunately, customers branches legal names does NOT always start with the first word of head office. I apologies, I should have mentioned it earlier.
Just a thought, can we not use the cust ID to do the search and if the ID is that of a branch then roll the amount to the respective head office.

In sheet2 does the customer information always in block: 1 empty row between customer
Yes, the customer information in sheet 2 is in block for reference purposes.

Please let me know if you need any more information or have any questions.

Thanks a lotttt again for looking into this.

Regards,
mohdshahtab

Thanks Ford for the reply.

Customer ID is just a random number picked by the system irrespective of the main office or branch or for particular customer and is unique. Customer IDs are usually 8 digits long and can be only numeric or alpha numeric.

Hope this clarification helps, but please do let me know if you need any further information / clarification.

Thanks a lot again.

Regards,
mohdshahtab

Thanks PCI for the file and apologies for the delay in getting back to you.

I would require one more help on this,

I do not want to add stand alone customers to the master data (as there are many of those) and want to keep only the data related to main office and it's branches.

And then, would like to get the sum total only of the branches and main office in the result save tab (macro already does this). However, once the total sum amount is available in Result Save tab, I want the main office and branch office rows to get deleted from the 'Raw data' tab, so that I can copy the data from 'Result save' tab (i.e., total of amount receivable for each main branch) and paste in the last row of 'Raw data' tab.

This means I only have data of amount receivable for each individual customer.

Please do let me know if you need any further clarification / have any questions. I will reply to you asap.

Regards,
mohdshahtab

Can someone please help with the above post.