Total Per Month @ Sumproduct w/ Table

[exc4libur]

Hello,
How is everyone?

I am trying to find a better solution to calculate the column named "Total Per Month" using the minimum amount of columns possible.

Please find attached an example workbook.

Thanks in advance!
exc4.

 

[prasaddn]

Hi,

Also pls clarify what si the logic of extracting % from table1?

Regards
Prasad DN

 

[exc4libur]

Hi,

Also pls clarify what si the logic of extracting % from table1?

Regards
Prasad DN

Hey Prasad,

The % from table1 is my "projection" of how much will be payed from the "Value ($)" per month after the "Date".

So for instance, Table1/Row[1]:

Date = 5/31/15;
Value ($) = 100;
Payments = 6 *months for the total value to be payed in full.

1 = "10%"
The First month after "5/31/15" (Date), "6/30/15", is the date I will have pay "10%" of Value ($) "100" = $10;

2 = "10%"
The Second month after "5/31/15" (Date), "7/31/15", is the date I will have pay "10%" of Value ($) "100" = $10;

6 = "50%"
The Sixth month after "5/31/15" (Date), "11/30/15", is the date I will have pay "50%" of Value ($) "100" = $50.

Did that help?

Regds,
Exc4.

 

[Jake Collins]

Dear Exc4

It is a bit long but I think the following array formula (in A16 and copied down) does what you want without any helper columns

=SUM(Table1[Value ($)]*Table1[1]*(1=Table1[Payments]+ROUND(DAYS360((DATE(YEAR(Table1[Date]),MONTH(Table1[Date])+Table1[Payments]+1,0)),A16)/30,0)))+SUM(Table1[Value ($)]*Table1[2]*(2=Table1[Payments]+ROUND(DAYS360((DATE(YEAR(Table1[Date]),MONTH(Table1[Date])+Table1[Payments]+1,0)),A16)/30,0)))+SUM(Table1[Value ($)]*Table1[3]*(3=Table1[Payments]+ROUND(DAYS360((DATE(YEAR(Table1[Date]),MONTH(Table1[Date])+Table1[Payments]+1,0)),A16)/30,0)))+SUM(Table1[Value ($)]*Table1[4]*(4=Table1[Payments]+ROUND(DAYS360((DATE(YEAR(Table1[Date]),MONTH(Table1[Date])+Table1[Payments]+1,0)),A16)/30,0)))+SUM(Table1[Value ($)]*Table1[5]*(5=Table1[Payments]+ROUND(DAYS360((DATE(YEAR(Table1[Date]),MONTH(Table1[Date])+Table1[Payments]+1,0)),A16)/30,0)))+SUM(Table1[Value ($)]*Table1[6]*(6=Table1[Payments]+ROUND(DAYS360((DATE(YEAR(Table1[Date]),MONTH(Table1[Date])+Table1[Payments]+1,0)),A16)/30,0)))+SUM(Table1[Value ($)]*Table1[7]*(7=Table1[Payments]+ROUND(DAYS360((DATE(YEAR(Table1[Date]),MONTH(Table1[Date])+Table1[Payments]+1,0)),A16)/30,0)))+SUM(Table1[Value ($)]*Table1[8]*(8=Table1[Payments]+ROUND(DAYS360((DATE(YEAR(Table1[Date]),MONTH(Table1[Date])+Table1[Payments]+1,0)),A16)/30,0)))+SUM(Table1[Value ($)]*Table1[9]*(9=Table1[Payments]+ROUND(DAYS360((DATE(YEAR(Table1[Date]),MONTH(Table1[Date])+Table1[Payments]+1,0)),A16)/30,0)))+SUM(Table1[Value ($)]*Table1[10]*(10=Table1[Payments]+ROUND(DAYS360((DATE(YEAR(Table1[Date]),MONTH(Table1[Date])+Table1[Payments]+1,0)),A16)/30,0)))+SUM(Table1[Value ($)]*Table1[11]*(11=Table1[Payments]+ROUND(DAYS360((DATE(YEAR(Table1[Date]),MONTH(Table1[Date])+Table1[Payments]+1,0)),A16)/30,0)))+SUM(Table1[Value ($)]*Table1[12]*(12=Table1[Payments]+ROUND(DAYS360((DATE(YEAR(Table1[Date]),MONTH(Table1[Date])+Table1[Payments]+1,0)),A16)/30,0)))+SUM(Table1[Value ($)]*Table1[13]*(13=Table1[Payments]+ROUND(DAYS360((DATE(YEAR(Table1[Date]),MONTH(Table1[Date])+Table1[Payments]+1,0)),A16)/30,0)))+SUM(Table1[Value ($)]*Table1[14]*(14=Table1[Payments]+ROUND(DAYS360((DATE(YEAR(Table1[Date]),MONTH(Table1[Date])+Table1[Payments]+1,0)),A16)/30,0)))+SUM(Table1[Value ($)]*Table1[15]*(15=Table1[Payments]+ROUND(DAYS360((DATE(YEAR(Table1[Date]),MONTH(Table1[Date])+Table1[Payments]+1,0)),A16)/30,0)))+SUM(Table1[Value ($)]*Table1[16]*(16=Table1[Payments]+ROUND(DAYS360((DATE(YEAR(Table1[Date]),MONTH(Table1[Date])+Table1[Payments]+1,0)),A16)/30,0)))+SUM(Table1[Value ($)]*Table1[17]*(17=Table1[Payments]+ROUND(DAYS360((DATE(YEAR(Table1[Date]),MONTH(Table1[Date])+Table1[Payments]+1,0)),A16)/30,0)))+SUM(Table1[Value ($)]*Table1[18]*(18=Table1[Payments]+ROUND(DAYS360((DATE(YEAR(Table1[Date]),MONTH(Table1[Date])+Table1[Payments]+1,0)),A16)/30,0)))

 

[prasaddn]

Hi,

Here is what best i could get:

=
SUM(IFERROR(((A16=EOMONTH($A$8,COLUMN(INDEX($1:$1,1):INDEX($1:$1,COLUMNS($A:U)))))/1)*($D$8:$U$8),0)*$B$8)+
SUM(IFERROR(((A16=EOMONTH($A$9,COLUMN(INDEX($1:$1,1):INDEX($1:$1,COLUMNS($A:U)))))/1)*($D$9:$U$9),0)*$B$9)+
SUM(IFERROR(((A16=EOMONTH($A$10,COLUMN(INDEX($1:$1,1):INDEX($1:$1,COLUMNS($A:U)))))/1)*($D$10:$U$10),0)*$B$10)+
SUM(IFERROR(((A16=EOMONTH($A$11,COLUMN(INDEX($1:$1,1):INDEX($1:$1,COLUMNS($A:U)))))/1)*($D$11:$U$11),0)*$B$11)

Entered as CSE formula.

Attached is the file with solution.

Regards,
Prasad DN

 

[exc4libur]

Thank you guys, but its too big and I need it to be dynamic.

Maybe the trick is to just get the job done and use the columns..

Thanks again for helping!!!