hi there,
after days of scanning through fora etc, i am still stuck. Cannot get my formula to work properly.
what i want is that cell D23 (in red font) returns the sum of certain values (the quarter after the submission month = the 3 columns after column of submission month) from Tab 2 depending on which category (Fixed costs, Project 1, etc). the first part of the formula to pick up the value if "NEXT Month" is selected works...its just the part of the formula to get NEXT QUARTER summed up.
Formula so far : =IFNA(IF($D$6="Next Month";INDEX('Country (EURO)'!$D$68:$AY$93;MATCH(A32;'Country (EURO)'!$A$68:$A$93;0);'Country (EURO)'!$D$2+1); SUMproduct(INDEX('Country (EURO)'!$D$68:$AY$93;MATCH(A32;'Country (EURO)'!$A$68:$A$93;0);'Country (EURO)'!$D$4:$AY$4;'Country (EURO)'!$A$68:$AY$93));" ")
the part in RED doesn't work. see below tables for background.
Appreciate your collective brainpower!
[TABLE="width: 567"]
<tbody>[TR]
[TD]TAB 1:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]Country:[/TD]
[TD][/TD]
[TD]BANGLADESH[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]Submission month:[/TD]
[TD][/TD]
[TD]Oct-16[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A6[/TD]
[TD]Request for period:[/TD]
[TD][/TD]
[TD]Next Quarter[/TD]
[/TR]
[TR]
[TD]A7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A21[/TD]
[TD]Net Cash Flow by category:[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A22[/TD]
[TD][/TD]
[TD] project ID:[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A23
[/TD]
[TD]Fixed Costs[/TD]
[TD][/TD]
[TD] which formula to put here?[/TD]
[/TR]
[TR]
[TD]A24[/TD]
[TD]Project 1[/TD]
[TD]600001[/TD]
[TD] xxx[/TD]
[/TR]
[TR]
[TD]A25[/TD]
[TD]Project 2[/TD]
[TD]600002[/TD]
[TD] xxx[/TD]
[/TR]
</tbody>[/TABLE]
Country (EURO) tab:
[TABLE="width: 100%"]
<tbody>[TR]
[TD="colspan: 16"]Consolidated Country Cashflow Planning BY PROJECT IN EURO[/TD]
[/TR]
[TR]
[TD]submission month[/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]column nrs:[/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]true/false:[/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Month[/TD]
[TD="colspan: 2"][/TD]
[TD]Jan-16[/TD]
[TD]Feb-16[/TD]
[TD]Mar-16[/TD]
[TD]Apr-16[/TD]
[TD]May-16[/TD]
[TD]Jun-16[/TD]
[TD]Jul-16[/TD]
[TD]Aug-16[/TD]
[TD]Sep-16[/TD]
[TD]Oct-16[/TD]
[TD]Nov-16[/TD]
[TD]Dec-16[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fixed Costs[/TD]
[TD]EUR[/TD]
[TD="colspan: 2"][/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 20,000
[/TD]
[TD] -[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]EUR[/TD]
[TD="colspan: 2"][/TD]
[TD] 4,047[/TD]
[TD] -[/TD]
[TD] 5,775[/TD]
[TD] (1,728)[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 5,775[/TD]
[TD] 9,047
[/TD]
[TD] (113,985)[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]EUR[/TD]
[TD="colspan: 2"][/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -
[/TD]
[TD] (10,000)[/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD]EUR[/TD]
[TD="colspan: 2"][/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
after days of scanning through fora etc, i am still stuck. Cannot get my formula to work properly.
what i want is that cell D23 (in red font) returns the sum of certain values (the quarter after the submission month = the 3 columns after column of submission month) from Tab 2 depending on which category (Fixed costs, Project 1, etc). the first part of the formula to pick up the value if "NEXT Month" is selected works...its just the part of the formula to get NEXT QUARTER summed up.
Formula so far : =IFNA(IF($D$6="Next Month";INDEX('Country (EURO)'!$D$68:$AY$93;MATCH(A32;'Country (EURO)'!$A$68:$A$93;0);'Country (EURO)'!$D$2+1); SUMproduct(INDEX('Country (EURO)'!$D$68:$AY$93;MATCH(A32;'Country (EURO)'!$A$68:$A$93;0);'Country (EURO)'!$D$4:$AY$4;'Country (EURO)'!$A$68:$AY$93));" ")
the part in RED doesn't work. see below tables for background.
Appreciate your collective brainpower!
[TABLE="width: 567"]
<tbody>[TR]
[TD]TAB 1:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]Country:[/TD]
[TD][/TD]
[TD]BANGLADESH[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]Submission month:[/TD]
[TD][/TD]
[TD]Oct-16[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A6[/TD]
[TD]Request for period:[/TD]
[TD][/TD]
[TD]Next Quarter[/TD]
[/TR]
[TR]
[TD]A7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A21[/TD]
[TD]Net Cash Flow by category:[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A22[/TD]
[TD][/TD]
[TD] project ID:[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A23
[/TD]
[TD]Fixed Costs[/TD]
[TD][/TD]
[TD] which formula to put here?[/TD]
[/TR]
[TR]
[TD]A24[/TD]
[TD]Project 1[/TD]
[TD]600001[/TD]
[TD] xxx[/TD]
[/TR]
[TR]
[TD]A25[/TD]
[TD]Project 2[/TD]
[TD]600002[/TD]
[TD] xxx[/TD]
[/TR]
</tbody>[/TABLE]
Country (EURO) tab:
[TABLE="width: 100%"]
<tbody>[TR]
[TD="colspan: 16"]Consolidated Country Cashflow Planning BY PROJECT IN EURO[/TD]
[/TR]
[TR]
[TD]submission month[/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]column nrs:[/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]true/false:[/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Month[/TD]
[TD="colspan: 2"][/TD]
[TD]Jan-16[/TD]
[TD]Feb-16[/TD]
[TD]Mar-16[/TD]
[TD]Apr-16[/TD]
[TD]May-16[/TD]
[TD]Jun-16[/TD]
[TD]Jul-16[/TD]
[TD]Aug-16[/TD]
[TD]Sep-16[/TD]
[TD]Oct-16[/TD]
[TD]Nov-16[/TD]
[TD]Dec-16[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fixed Costs[/TD]
[TD]EUR[/TD]
[TD="colspan: 2"][/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 20,000
[/TD]
[TD] -[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]EUR[/TD]
[TD="colspan: 2"][/TD]
[TD] 4,047[/TD]
[TD] -[/TD]
[TD] 5,775[/TD]
[TD] (1,728)[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 5,775[/TD]
[TD] 9,047
[/TD]
[TD] (113,985)[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]EUR[/TD]
[TD="colspan: 2"][/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -
[/TD]
[TD] (10,000)[/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD]EUR[/TD]
[TD="colspan: 2"][/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]