Make sum 0 when at least one element is null

[Anonymous]

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

[Anonymous]

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)