1 Reply Latest reply: Jan 20, 2012 12:23 PM by Valeriy Shylin RSS

    Make sum 0 when at least one element is null

    Valeriy Shylin

      Hi guys,

       

      I have Product, Market dimensions and a table of values.

      Market is divided into Markets and Market Areas.

       

      What I want to do is to sum the data on Market Areas and return 0 each time at least one market is 0 or missing.

       

      Example:

       

      Markets:

      Market     MarketArea

      UK          EU

      France     EU

      Italy         EU

      UK          EU27

      Italy         EU27

      Germany  EU27

       

      Demand:

      Market     Product     Demand

      UK          Prod1        10

      France     Prod1        5

      Italy         Prod1       11

      UK          Prod2        5

       

      So,

      1) the sum per market area should result in this:

      MarketArea     Demand

      EU                 10+5+11+5

      EU27              0 (since Germany is missing)

       

      2) but the sum per market area per product should result in this:

      MarketArea     Product     Demand

      EU                  Prod1        10+5+11

      EU                  Prod2        0 (instead of 5 since Italy and France are missing)

      EU27               Prod1       10 + 11

      EU 27              Prod2       0 (instead of 5 since Italy and Germany are missing)

       

      How can I do this?

       

      Valera

        • Make sum 0 when at least one element is null
          Valeriy Shylin

          Correction:

           

          2) but the sum per market area per product should result in this:

          MarketArea     Product     Demand

          EU                  Prod1        10+5+11

          EU                  Prod2        0 (instead of 5 since Italy and France are missing)

          EU27               Prod1       0 (since Germany is missing)

          EU 27              Prod2       0 (instead of 5 since Italy and Germany are missing)