Sum values in multiple columns with same index number

[vikrampnz]

Hello

I have made a cash flow sheet to show Apartment Sales Inflow per month. (Please see attached sheet)

I have created various Rules in sheet "Gaps" showing when proportionate payment will be received for each construction stage of a particular apartment, by putting Month number under each stage in the table.

Second Sheet has Unit number wise costing details for each Apartment and the stagewise cost break up for each construction stage.

Third Sheet, which is my cash Flow sheet shows the Money inflow for each month based on when each apartment was sold and which construction stages are complete for respective apartments.

Now this sheet works perfectly well for me for Apartments which are sold at begining of the construction cycle.

My problem is, when I assume a sale of an Apartment at a later date when my first 5 construction stages are completed, I expect that the Sum of those completed stages should reflect in CashFlow sheet (See Rule number KR0319 in sheet GAPS, where first five stages are shown complete in Month 1) under the Sale Month in CashFlow sheet, which will be the First payment Month for that particular apartment.

Can someone please help ! Would really appreciate it.

Thanks

[JeteMc]

If I understand correctly then using the following array entered formula* may help:

Please Login or Register  to view this content.

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Note that in the attached file zero values have been hidden using conditional formatting.
Let us know if you have any questions.

[vikrampnz]

Hello JeteMc

It works brilliantly. Does exactly what I was looking for !!

Thank you so much for your help , Really appreciate it!!

I am trying it in my main sheet without any issues so far, will come back to you if I face any issues.

Thanks again !

[JeteMc]

You're Welcome and thank you for the feedback. If no issues arise please take a moment to mark the thread as 'Solved' using the thread tools drop down in the ribbon above your first post. I hope that you have a blessed day.

[vikrampnz]

Hello JeteMc

I have encountered a slight problem. If I do leave "Rule" column i.e. cell in Column D Blank, it shows a strangely large sum /value in "column E".

Actually, if the Rule field in column D is Blank, then it should show "0" in ALL cells under Months columns i.e. from Column E to AU.

Could you please help !

Thanks

[vikrampnz]

Hello JeteMc

I just discovered that if I change the starting month to a later month i.e. if I change the Month in E3 from Sep-18 to Oct-18, it pushes those large numbers in Column E to following columns i.e. I , J

waiting for your help ! thanks in advance

[JeteMc]

As to post #5 modify the formula to read:

Please Login or Register  to view this content.

Remember to simultaneously press the Ctrl, Shift and Enter keys after making the change and before copying to other cells.
As to post #6 when I change the date in cell E3 on the MS-Cashflow sheet from 8/1/2018 to 9/1/2018 (I am working with the file attached to post #2) it causes cells C4:C8 to display #N/A and E4:AU8 to display blanks, so I imagine that I am missing something else that needs to be done when that date is changed. Please either explain all of the changes made, or upload another file that demonstrates the issue.
Let us know if you have any questions.

[vikrampnz]

Hello JeteMc

With your new formula, its solves my problem from post#5 and reference to post# I am getting the same error #N/A, as you if I change the month. I am no longer having the figures pushed to further month, as earlier experienced.

wondering why?

[JeteMc]

I think that it may be because the dates in K5:K9 of the 'MS-PS' sheet are 8/1/2018. After changing E3 (MS-Cashflow sheet) to 9/1/2018, if you also change the date in K5 (MS-PS sheet) to 9/1/2018 then cell C4 on the 'MS-Cashflow' sheet displays 5 and values are displayed in E4:L4
Let us know if you have any questions.

[vikrampnz]

You are right ! Cashflow Month Range has to be same as the Sale Date Range in MS-PS sheet, else it gives error. I think I can manage this issue. Thanks again for your prompt help !!