Formula to return total of sub-accounts to the main account

Hello everyone. I am stuck with this problem. I basically need a formula that adds up the total of sub-accounts and posts that value against the Parent Account. I have a large data sheet so entering manually would take a lot of time. I have attached an excel sheet where i have highlighted in colors the Parent account and their sub accounts. I have manually entered the totals of sub-accounts in column C but i need a formula which would do it automatically.

To identify how a parent account relates to a sub account is a parent Account say has code 4-001-002 and a sub account has say code 4-001-002-011. The 002 is the identification that the sub account relates to the parent. It would be much easier to understand in the excel sheet i have attached.

any help would be much needed.

Hello,

In your attached sample, try this formula in C2:

Please Login or Register  to view this content.

Drag it down however long you want. Is this what you were looking for?

Glad that I could help.

If you have found a fitting solution to your problem, please mark the Thread as [SOLVED] using the Thread tools right above post #1. It keeps things neat and tidy.

And have a great day!

That's because you didn't adjust the range to fit the new data.
The old formula was

Please Login or Register  to view this content.

And the new one should look like this:

Please Login or Register  to view this content.

Since you inserted a row.

Let me know if it works with your data.

Is the data in E37:E49 supposed to represent a different month of data? There doesn't appear to be any sort of markers that would separate it from the data above. I believe you're missing a column for Month or a date.

Do the subaccounts occur once per month? Maybe you want to add up the Nth occurence?

For example:

4-001-001-003 : MEDICAL INSURANCE	10
4-001-001-001 : REPAIR & MAINT - OFFICE EQUIPMENT	10
4-001-001-003 : MEDICAL INSURANCE	20
4-001-001-001 : REPAIR & MAINT - OFFICE EQUIPMENT	20
4-001-001-003 : MEDICAL INSURANCE	30
4-001-001-001 : REPAIR & MAINT - OFFICE EQUIPMENT	30
Totals
Month 1: 4-001-001-003	20
Month 2: 4-001-001-003	40
Month 3:4-001-001-003	60

I'm not sure I understand what your spreadsheet isn't doing. Everything appears to be working correctly. If you decide to add more data, you'll need to change your formula to accommodate the extended range.

Hi daffodil11. I have attached the complete workbook named "Expense Report (Monthly)".

Actually the formula is not working the way it should. It does pick the values but not the proper values. I think the formula needs to be changed which i am unable to do so.

Its the Expense Report tab which shows the columns B,C and D in Sheet1. Its a fixed format which repeats after row43 in Sheet1. I have put in formulas in Expense Report which picks it up from Sheet1 using drop down list against "Jan-Apr" & "May".

Column F in Sheet1 are sub-accounts. For example F2:F11 in Sheet1 are sub accounts that belong to the Parent account B2. How to know these match up is by looking at their codes. B2 is 4-001-001 and say F6 is 4-001-001-006. The last 006 is the sub code while the rest of the code for both matches. If you use =Lens formula its the first 9 characters from LEFT that match.

I have manually written the totals in column J in Sheet1 just to show how the column D should show the totals but as i autofill the formula it does not pick the right values in D.

If you look at Column G in Sheet 1 it is adding F2:F11 and F37:F44 and posting the result in D2. I want F2:F11 to post in D2 and F37:F44 in D44.

Yes you are right the F2:F36 repeats after the row36 because i copy pasted the data for the month of May with different values.

I hope u understand what i am stuck with. Kindly help me out as i would be adding in data for each month at that month end in column F and G and whose values would be automatically updated in column D.

See, your real version has months in it, which is what Master Lemice was talking about.

This should work fine:

Please Login or Register  to view this content.

Peace be with you.