Lookup & Expense Allocation Formula

I'm trying to create a lookup function that looks up associated costs for certain cells and distributes them onto an expense sheet based on the month and evenly splits the costs based on how many units are allocated to it.

1. Looks up Costs associated with Unit (Unit1,Unit2,Unit3, or Unit4)
2. Adds associated costs to associated Month
3. Evenly splits costs based on how many Units are in Unit Allocation list.

Example:

Worksheet "Renovation Expenses"
Expense amount = $100
Unit Allocation = Unit1, Unit2
Date = 7/5/17

Results wanted
Worksheet "Financials"
Cell: A July to show $50
Cell: B July to show $50

Other instances: If unit allocation shows "All" I want it to distribute to all units (A,B,C,D) evenly.

Attached Excel File with wanted results posted.

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

Thanks, attached file!

Are the unit names REALLY A B C & D? if so, this creates some problems because it will be fiddly to search for "A" and not also find "All"

No Unit names can be anything, I changed them to "Unit1, Unit2, Unit3, Unit4" but if that doesn't work as well then whatever makes the function easiest will do.

Chrapm1 - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

I went ahead anyway. I do need a helper column, in Renovation Expenses, E3, copied down:

=IF(C3="All",COUNTIF($I$3:$I$7,"<>All"),1+LEN(C3)-LEN(SUBSTITUTE(C3,",","")))

then this in Financials, B3, copied across and down:

=SUMPRODUCT((ISNUMBER(SEARCH($A3,'Renovation Expenses'!$C$3:$C$10))*(MONTH('Renovation Expenses'!$D$3:$D$10)=MONTH(1&B$2))*('Renovation Expenses'!$C$3:$C$10<>"All")*'Renovation Expenses'!$B$3:$B$10)/'Renovation Expenses'!$E$3:$E$10)+SUMPRODUCT(('Renovation Expenses'!$C$3:$C$10="All")*(MONTH('Renovation Expenses'!$D$3:$D$10)=MONTH(1&B$2))*'Renovation Expenses'!$B$3:$B$10/'Renovation Expenses'!$E$3:$E$10)

Show us how your layout will be modified, both in the source data and your expected results...

maybe post a modified sheet???

1. helper modified to remove the 1's against blanks:
=IF(A3="","",(IF(C3="All",COUNTIF($I$3:$I$7,"<>All"),1+LEN(C3)-LEN(SUBSTITUTE(C3,",","")))))

Dates along top of desired results, changed to real dates and formatted as mmm-yy

in B5, copied across/down all of 2017:
=SUMPRODUCT((ISNUMBER(SEARCH($A5,Expenses!$C$3:$C$10))*(Expenses!$D$3:$D$10>=B$2)*(Expenses!$D$3:$D$10<=EOMONTH(B$2,0))*(Expenses!$C$3:$C$10<>"All")*Expenses!$B$3:$B$10)/Expenses!$E$3:$E$10)+SUMPRODUCT((Expenses!$C$3:$C$10="All")*(MONTH(Expenses!$D$3:$D$10)=MONTH(B$2))*Expenses!$B$3:$B$10/Expenses!$E$3:$E$10)

then select all of 2017 and copy/paste onto 2018. It would be easier to manage with some dynamic named ranges on the source sheets. Do you know how to set these up? If not, in your real data , are the data the result of formulae elsewhere, or are these the directly entered/imported values?

It's 'cos I'm a tube... look back in a moment.

Fixed. The totals were wrong, too. But that was your mistake, not mine!! Also fixed.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.