2 Dimensional SUMIF

[AMerchant]

I would like to summarise the sales data below by region and by year. Could someone suggest a formula that sums based on both horizontal and vertical criteria?
Sales Data

Period 1 1 2 2 3 3

UK Product A 500 700 450 600 550 500
UK Product B 200 400 250 350 400 450
USA Product A 200 350 210 450 250 400
USA Product B 100 150 250 300 310 350
Asia Product A 1200 1500 1000 1100 1200 1250
Asia Product B 1000 1250 950 1000 1100 1000

Sales Summary
1 2 3
UK
USA
Asia

[Sam Capricci]

it is a little difficult to see what you are trying to sum, can you post a small sample so people can see what you have and what you are looking for? Go advanced to accomplish this.

[teylyn]

Hello,

consider the following screenshot. The formula in cell K2 is

=SUMPRODUCT($C$3:$H$8*($A$3:$A$8=$J2)*($C$1:$H$1=K$1))

copied across and down.

2015-05-21_21-21-06.png

cheers, teylyn

[AMerchant]

Many Thanks. The SUMPRODUCT formulae works fine. Problem Solved.