SUM all raw data by code IF main cell contains its code
Hello guys!
I have an issue - which is not classic partial SUMIFS problem - at least I do not I think it is.
I have a detailed raw-data expenditure sheet, with each transaction recorded per budget code.
I need to sum all expenditures in the budget template - but the catch is that some of the budget lines have multiple budget codes assigned.
The tables look something like this:
RAW data:
RAW_DATA.png
BUDGET:
BUDGET.png
Any thought on this?
Thank you very much <3
Welcome to the forum.
There are instructions at the top of the page explaining how to attach your sample workbook.
Where in the world is B&H???
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Bosnia and Herzegovina - Europe
Sorry - still getting used to the forum. I am not sure if I attached it correctly?
Thanks - I'll have a look.
Please update your profile to something that everyone will understand regarding your location (Bosnia and Hezegovina OR Europe will do). B&H is not going to mean anything to most people. Thanks.
OK - you have multiple items in cells in column A - can these be avoided? They are going to complicate the issue.
Thank you very much for your efforts.
Unfortunately no - that is the core of my problem. I have not budgeted this. Usually I do everything in my power to avoid assigning multiple codes to budget line.
caution : you have to separate both budgetcodes only with character 10 (=ALT+Enter), no ";"-character !!!!
perhaps it's better to do it in 2 cells
Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Edit: There was a mistake in my last formula. I neglected to remove the ";" characters.
If your set can tolerate a helper column this will work.
Helper column formula in 'Budget' column E counts the number of Budget codes in each cell of column BFormula:
Please Login or Register to view this content.
In D2 and filled down.Formula:Please Login or Register to view this content.
Last edited by FlameRetired; 09-23-2021 at 03:45 PM.
Dave
BUDGET
D2=SUMPRODUCT((ISNUMBER(SEARCH('RAW DATA'!$A$2:$A$16,BUDGET!B2)))*('RAW DATA'!$B$2:$B$16))
Copy down until D7
D9=SUMPRODUCT((ISNUMBER(SEARCH('RAW DATA'!$A$2:$A$16,BUDGET!$B9)))*('RAW DATA'!$B$2:$B$16))
Last edited by CARACALLA; 09-23-2021 at 04:54 PM.
Works like a charm! Thank you very, very much - you guys are awesome
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Just one tip for all those who come to this tread looking for the solution to same problem:
Please make sure that both of your sheets start from the same row, as it is case in mines (e.i. budget calculations and first formula starts from the row 2 in BUDGET sheet, and transactions start from the row 2 as well in the sheet RAW DATA).
Otherwise formula won't work for this case.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks