sumproduct in combination with index/match or vlookup won't work. Array sizes different?

sleur

New Member
Joined
Nov 3, 2016
Messages
1
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]
 

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top