FlagInFormula_Create sumproduct formula to replace column in table

[Fl3kken]

Hi,

(First post. Have not yet figures out how to attach example-spreadsheet.)

I have a problem trying to incorporate a formula into sumproduct formula.

In one table (TableBOSales) I have four columns (År Måned, SAP Avdelingsnummer, Antall, and Flag 1).
The flag column matches identifier to another table (TableDCF) and if corresponding date meets a set of criteria, the value will be 1 or 0.
By multiplying the Flag column with "Antall" using sumproduct (=SUMPRODUCT(TableBOSales[Antall];TableBOSales[Flagg 1])), I will get the sum of [Antall] where the criterias are met.
This works fine.

However, I would like to incorporate the formula for calculating "Flagg 1" into the sumproduct formula, allowing me to sum without using the Flagg 1 column.

This is the formula in column [Flagg 1]:
(TableBOSales[År Måned]>VALUE(TEXT(INDEX(TableDCF[Lanseringsdato];MATCH(TableBOSales[SAP Avdelingsnummer];TableDCF[SAPnr];0));"ååååmm")))*(TableBOSales[År Måned]<=VALUE(TEXT(EDATE(INDEX(TableDCF[Lanseringsdato];MATCH(TableBOSales[SAP Avdelingsnummer];TableDCF[SAPnr];0));12);"ååååmm")))*(TableBOSales[År Måned]<=VALUE(201405))

In the table, the formula above calculates just fine.
When I try to incorporate it into the sumproduct formula, it only matches the first identifier (first value in TableBOSales[SAP Avdelingsnummer]). Hence it summarizes all the values wheter or not the criteria are met. It does not seem to like the Index part of the formula.

The final formula, which is not yet working:
=SUMPRODUCT(TableBOSales[Antall];(TableBOSales[År Måned]>VALUE(TEXT(INDEX(TableDCF[Lanseringsdato];MATCH(TableBOSales[SAP Avdelingsnummer];TableDCF[SAPnr];0));"ååååmm")))*(TableBOSales[År Måned]<=VALUE(TEXT(EDATE(INDEX(TableDCF[Lanseringsdato];MATCH(TableBOSales[SAP Avdelingsnummer];TableDCF[SAPnr];0));12);"ååååmm")))*(TableBOSales[År Måned]<=VALUE(201405)))


Excerpt of tables:

[TABLE="width: 373"]
<tbody>[TR]
[TD]År Måned[/TD]
[TD]SAP Avdelingsnummer[/TD]
[TD]Antall[/TD]
[TD]Flagg 1[/TD]
[/TR]
[TR]
[TD]201401[/TD]
[TD]7141047500[/TD]
[TD]9.060[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]201403[/TD]
[TD]7141047500[/TD]
[TD]2.825[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]201403[/TD]
[TD]7141047500[/TD]
[TD]331[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]201403[/TD]
[TD]7141047500[/TD]
[TD]6.137[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]201403[/TD]
[TD]7141047500[/TD]
[TD]564[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]201402[/TD]
[TD="align: right"]7143077500 [/TD]
[TD]1.000[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]201402[/TD]
[TD="align: right"]7143077500 [/TD]
[TD]1.000[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]201403[/TD]
[TD]7141847500[/TD]
[TD]7.353[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]201403[/TD]
[TD]7141847500[/TD]
[TD]2.622[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]201403[/TD]
[TD]7141847500[/TD]
[TD]253[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]201403[/TD]
[TD]7141847500[/TD]
[TD]7.645[/TD]
[TD]1[/TD]
[/TR]
</tbody><colgroup><col><col><col span="2"></colgroup>[/TABLE]

[TABLE="width: 295"]
<tbody>[TR]
[TD]SAPnr[/TD]
[TD]Lanseringsdato[/TD]
[TD]Lanseringsmåned[/TD]
[/TR]
[TR]
[TD="align: right"]7141047500[/TD]
[TD] 20.06.2013[/TD]
[TD]201306[/TD]
[/TR]
[TR]
[TD="align: right"]7142237500[/TD]
[TD] 03.02.2014[/TD]
[TD]201402[/TD]
[/TR]
[TR]
[TD="align: right"]7142207500 [/TD]
[TD] 20.02.2014[/TD]
[TD]201402[/TD]
[/TR]
[TR]
[TD="align: right"]7141847500[/TD]
[TD] 30.06.2013[/TD]
[TD]201306[/TD]
[/TR]
[TR]
[TD="align: right"]7141537500[/TD]
[TD] 20.08.2013[/TD]
[TD]201308[/TD]
[/TR]
[TR]
[TD="align: right"]7143077500[/TD]
[TD] 27.02.2014[/TD]
[TD]201402[/TD]
[/TR]
[TR]
[TD="align: right"]7142877500[/TD]
[TD] 30.11.2013[/TD]
[TD]201311[/TD]
[/TR]
[TR]
[TD="align: right"]7141477500[/TD]
[TD] 25.11.2013[/TD]
[TD]201311[/TD]
[/TR]
[TR]
[TD="align: right"]7141287500[/TD]
[TD] 03.04.2014[/TD]
[TD]201404[/TD]
[/TR]
[TR]
[TD="align: right"]7143087500[/TD]
[TD] 12.05.2014[/TD]
[TD]201405[/TD]
[/TR]
[TR]
[TD="align: right"]7143157500[/TD]
[TD] 05.06.2014[/TD]
[TD]201406[/TD]
[/TR]
[TR]
[TD="align: right"]7141517500[/TD]
[TD] 10.04.2014[/TD]
[TD]201404[/TD]
[/TR]
[TR]
[TD="align: right"]7141897500[/TD]
[TD] 01.07.2014[/TD]
[TD]201407[/TD]
[/TR]
[TR]
[TD="align: right"]7143127500[/TD]
[TD] 19.06.2014[/TD]
[TD]201406[/TD]
[/TR]
[TR]
[TD="align: right"]7143107500[/TD]
[TD] 14.08.2014[/TD]
[TD]201408[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]