[shazydotcom]

Dear Excel lovers. Today after a long time I have come back here with a new problem. I hope some expert will help me in it.

I am working in some cash flow projections in excel. One sheet named "Monthly cashflows" contains the data for inflows and outflows. The other sheet named "loans" contains data of loans to be taken for the company in coming years. All loans have different dates and different amounts. How can I directly get the sums of loan installments in a particular month in the "Monthly cashflows" sheet from loans sheet? I have also attached the excel file for your perusal.

E.g, Sheet = Monthly cashflows

D33 = 25 million and it will start for payment in May 2011 as reflected in M17 of "Loans sheet".

Well I tried to create another sheet where I created links of all loans payments and then though to code the dates with desired text, thinking that after doing that I will use "sumif" function to get monthly payments but it would be very very long formula and process.

Please tell me how can I do it?

Thanks in advance.

Shazy

[JBeaucaire]

If you didn't have those dates in the little summary boxes above each table of payments, we could do this one simple SUMIF(). Since you do, you'll have use a SUMIF() for each column so as to not accidentally include those dates.

On your test sheet, you could put this G8 and copy down to get a sum of all the payment from all the loans for the date shown in D8:

=SUMIF(Loans!L:L, $D8, Loans!M:M) + SUMIF(Loans!U:U, $D8, Loans!V:V) + SUMIF(Loans!AD:AD, $D17,AE:AE ) + SUMIF(Loans!AM:AM, $D17,AN:AN )

[shazydotcom]

Thanks for your reply dear JBeaucaire. I will test the formula but in your formula in the third place you put $D17 instead of $D8. Is it true ?

Best regards,
Shazy

[shazydotcom]

Dear JBeaucaire.. The formula worked excellent... yesterday I summed up the values using Consolidation function and today again applied your formula to check and match the results. It was excellent except that it gave a difference at in five cells starting from November-2013 and in subsequent quarters.

I audited the values from Loans sheet and found that Consolidated data is giving true value. I don't know then why the formula is not giving the same results.

I have again attached the worksheet where in the Test Sheet, the consolidated results are in G8:H96, and with the formula the results are J9:K52. Column M shows the difference.

[JBeaucaire]

Yeah, my bad:

=SUMIF(Loans!L:L, $D8, Loans!M:M) + SUMIF(Loans!U:U, $D8, Loans!V:V) + SUMIF(Loans!AD:AD, $D8, Loans!AE:AE) + SUMIF(Loans!AM:AM, $D8, Loans!AN:AN)

[shazydotcom]

Dear JBeaucaire.. please also comment on the difference as I could not trace the reason... Your cooperation is appreciated in advance...

[JBeaucaire]

You had some missing $ in your first cell that caused the values to skew as you got far down the list. In K9:

=SUMIF(Loans!$L$17:$L$200, $J9, Loans!$M$17:$M$200) + SUMIF(Loans!$U$17:$U$165, $J9, Loans!$V$17:$V$165) + SUMIF(Loans!$AD$17:$AD$165, $J9, Loans!$AE$17:$AE$165) + SUMIF(Loans!$AM$17:$AM$165, $J9, Loans!$AN$17:$AN$165)

[shazydotcom]

Superb... I really appreciate you dear JBeaucaire that you spent your precious time in solving my problem. Hats off to you...

Best regards,
Shazy

[shazydotcom]

Referring to above... I was thinking about the scenarios and found that the above case had the first dates for all months as below:

WorkSheet=Test

J9 01-May-2011
J10 01-Jun-2011
J11 01-Jul-2011

And the formula in Column K fetched the results from Worksheet Loans, where the dates were also given exactly in the same manner as 01-May-2011, 01-Jun-2011, etc.

Now If the data in Loans Sheet has different dates even within a month like if loan installments are due on:

05-May-2011 SR 100,000
12-May-2011 SR 250,000
29-May-2011 SR 179,000

And in the Column J of Test Sheet we want the sum of loans installments falling in the month of May-2011 instead of our earlier query of 01-May-2011, then what will be the formula?

I hope I have explained the problem clearly, but if it is not the case, please advise.


Secondly:

When we copy and paste data it gives the option to paste as link so when the source data is changed the target cells are automatically changed. But if we go for the "transpose" option then it can not be pasted as link as in case of "transpose" selection the "paste link" options goes off. It means if the source data is in vertical form it can only be pasted in the vertical form for linkage to target cells. What if we wish to paste it in horizontal manner but also want to keep the links from source data? What can be the solution?

Best regards,
Shazy

[JBeaucaire]

Sorry, I don't actually follow either question.

[shazydotcom]

Ok.. I have attached the excel sheet again for your perusal.

There are two sheets viz a viz Loans and Test.

Loans Sheet includes the detail of loans and their payments falling due in each year i.e. 2011,2012,2013 & 2014. Loan payments date are different.

In the Test sheet I want the results as =J9 shows the month of a year i.e. May 2011, J10=June 2011 and so on.

Now from the Loans sheet we want to see that how many payments are due in the month of May 2011 or June 2011 and the sum of total due should appear in Test sheet at K9 and K10 and so on.

I hope now the query is more understandable.

Second Question:
Sheet1
A B C D E F
1 5 7 3 1 9 6
2
3
4
5

If we copy the data A1:F1 and want to paste them in Sheet2, we have the option to paste as link. So when I will change the data in Sheet1, it will automatically be changed in Sheet2. But if I paste the data with transpose it will appear like below:

A B C D E F
1 5
2 7
3 3
4 1
5 9
6 6

Now if I select to transpose the "paste link" option becomes disabled.

My question was is there a way that even the links remain there when I transpose the data. I know it can be done manually but I am looking if any other option is available.

Thank you.

[JBeaucaire]

Multi-criteria summing requires the use of SUMPRODUCT() to filter properly. The L9 formula would be:

=SUMPRODUCT(--(Loans!$L$17:$L$200>=$J9), --(Loans!$L$17:$L$200<=EOMONTH($J9,0)), Loans!$M$17:$M$200) +
SUMPRODUCT(--(Loans!$U$17:$U$165>=$J9), --(Loans!$U$17:$U$165<=EOMONTH($J9,0)), Loans!$V$17:$V$165) +
SUMPRODUCT(--(Loans!$AD$17:$AD$165>=$J9), --(Loans!$AD$17:$AD$165<=EOMONTH($J9,0)), Loans!$AE$17:$AE$165) +
SUMPRODUCT(--(Loans!$AM$17:$AM$165>=$J9), --(Loans!$AM$17:$AM$165<=EOMONTH($J9,0)), Loans!$AN$17:$AN$165)

Be careful, SUMPRODUCT() is volatile and adding too many of these functions to a workbook or using ranges too big can cause performance issues, sometimes big performance issues. It doesn't appear to be a problem with the sample data so far, but if you notice your workbook starting to calculate slowly, you'll know what's causing it.


EDIT:

EOMONTH() is part of the Analysis Toolpak. Activate that in Tools > Addins if necessary.

[shazydotcom]

Dear JB.. thanks for your reply.. I have tried the formula but it is not working here. I am using Excel 2007.

Excel Options ---> Add Ins ---> Active Application Add Ins include the Analysis Toolpak... I assume it is already active.. Is it ?

If you tried the formula on my workbook.. can you send that to me so I may see the results and verify where I am making mistake in formula on my side ?

Or please advise...

[shazydotcom]

I tried the formula with dashes -- and also without -- but it did not work here...

[shazydotcom]

JB... I have tried the formula at home and it worked perfectly.. what I noticed is that the data format is mm-dd-yyyyy at home while in office it was dd-mm-yyyy. Do you think it can be a reason for the formula to not to work in office? By the way in office the date format dd-mm-yyyy works without problem for other functions...

What is your opinion now ?

[shazydotcom]

Dear JB... It worked today :) I am happy... Thank you very much... It has worked successfully and I verified the results... I did not change the date format but wrote again the formula and it went smoothly...

Hats off to you JB... May God bless you...

[JBeaucaire]

Glad to help, sorry I was away for so long, real life calls...

It's not surprising to find that closing Excel and restarting afresh causes things to return to "normal", I've seen that myself many times.


Analysis ToolPack is an Excel 2003 addin. It's already part of Excel 2007.