SUM IF * IF THEN ELSE

[Laxbax]

Hello 

I'm having a problem getting my total value calculated based on "confirmation key".
So far i've managed to match the currency and multiply the net value by exchange rate.

I want to mulitply the converted value based on "Confirmation key" Where it should prioritze LA>AB>with no key

I'm trying to make an expression that first multiplies , all 'REM QTY' with Confirmation key = 'LA' ,
IF([Confirmation key]='LA',[REM QTY],

if the confirmation key is not <> LA then mulitply all 'REM QTY' with Confirmation key = 'AB'
IF([Confirmation key]<>'LA', [Confirmation key]='AB'*[REM QTY],

and if there is no 'AB,  then just use REM QTY
IF([Confirmation key]<>'AB',[REM QTY])))

At the moment it only gives me the value of all LA Confirmation keys, not all the AB keys that have no LA and no value of the confirmation keys that have no LA or AB

=SUM(
IF([Currency]='SEK', [Net Price_NETPR]* 1,
IF([Currency]='NOK', [Net Price_NETPR]* 1.089292,
IF([Currency]='DKK', [Net Price_NETPR]* 1.4145,
IF([Currency]='EUR', [Net Price_NETPR]* 10.5581,
IF([Currency]='GBP',[Net Price_NETP.]* 11.7546,
IF([Currency]='USD',[Net Price_NETPR]* 9.2713,)))))))
*
IF([Confirmation key]='LA',[REM QTY],
IF([Confirmation key]<>'LA', [Confirmation key]='AB'*[REM QTY],
IF([Confirmation key]<>'AB',[REM QTY])))

[JordyWegman · Accepted solution]

Hi Laxbax,

Try this:

=SUM(
IF([Currency]='SEK', [Net Price_NETPR]* 1,
IF([Currency]='NOK', [Net Price_NETPR]* 1.089292,
IF([Currency]='DKK', [Net Price_NETPR]* 1.4145,
IF([Currency]='EUR', [Net Price_NETPR]* 10.5581,
IF([Currency]='GBP',[Net Price_NETP.]* 11.7546,
IF([Currency]='USD',[Net Price_NETPR]* 9.2713,)))))))
*
IF([Confirmation key]='LA',[REM QTY],
IF([Confirmation key]<>'LA' and [Confirmation key]='AB',[REM QTY],
IF([Confirmation key]<>'AB',[REM QTY])))

But last part always return [REM QTY], so why not do this?

=SUM(
IF([Currency]='SEK', [Net Price_NETPR]* 1,
IF([Currency]='NOK', [Net Price_NETPR]* 1.089292,
IF([Currency]='DKK', [Net Price_NETPR]* 1.4145,
IF([Currency]='EUR', [Net Price_NETPR]* 10.5581,
IF([Currency]='GBP',[Net Price_NETP.]* 11.7546,
IF([Currency]='USD',[Net Price_NETPR]* 9.2713,)))))))
*
[REM QTY])

Or are you missing a field in the part: IF([Confirmation key]<>'LA', [Confirmation key]='AB' MISSINGFIELD *[REM QTY],

Then you can use:

=SUM(
IF([Currency]='SEK', [Net Price_NETPR]* 1,
IF([Currency]='NOK', [Net Price_NETPR]* 1.089292,
IF([Currency]='DKK', [Net Price_NETPR]* 1.4145,
IF([Currency]='EUR', [Net Price_NETPR]* 10.5581,
IF([Currency]='GBP',[Net Price_NETP.]* 11.7546,
IF([Currency]='USD',[Net Price_NETPR]* 9.2713,)))))))
*
IF([Confirmation key]='LA',[REM QTY],
IF([Confirmation key]<>'LA' and [Confirmation key]='AB',[MissingField] *[REM QTY],
IF([Confirmation key]<>'AB',[REM QTY])))

Jordy

Climber

[Laxbax]

Thank you, I was missing a crucial field