FlagInFormula_Create sumproduct formula to replace column in table

Fl3kken

New Member
Joined
Jul 1, 2014
Messages
1
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:

År MånedSAP AvdelingsnummerAntallFlagg 1
20140171410475009.0601
20140371410475002.8251
20140371410475003311
20140371410475006.1371
20140371410475005641
2014027143077500 1.000-
2014027143077500 1.000-
20140371418475007.3531
20140371418475002.6221
20140371418475002531
20140371418475007.6451

<tbody>
</tbody><colgroup><col><col><col span="2"></colgroup>

SAPnrLanseringsdatoLanseringsmåned
7141047500 20.06.2013201306
7142237500 03.02.2014201402
7142207500 20.02.2014201402
7141847500 30.06.2013201306
7141537500 20.08.2013201308
7143077500 27.02.2014201402
7142877500 30.11.2013201311
7141477500 25.11.2013201311
7141287500 03.04.2014201404
7143087500 12.05.2014201405
7143157500 05.06.2014201406
7141517500 10.04.2014201404
7141897500 01.07.2014201407
7143127500 19.06.2014201406
7143107500 14.08.2014201408

<tbody>
</tbody><colgroup><col><col><col></colgroup>
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

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