I have a set of data with countries and sectors and would like to tabulate
(sum) the data in one column of the rows meeting each of the two criteria. I
understand how to use sumif to collect data on either of the criteria
(summing data for country or sector in the example below), but don't know how
to collect the data meeting both criteria.
e.g. if the data set were the following:
country sector weight
china materials 1.4
china materials 3.0
china industrials 2.5
china technology 2.7
china industrials 1.3
brazil materials 4.3
brazil technology 2.1
russia materials 2.3
russia technology 3.1

I would like to find formulas which would help me fill out the following
array (x1:z3)
sector
country materials industrials technology
china x1 x2 x3
brazil y1 y2 y3
russia z1 z2 z3

Thank you in advance for your help!

Use SUMPRODUCT when you have more than one criteria to deal with:

=SUMPRODUCT(--($A$1:$A$100="China",--($B$1:$B$100="materials"),$C$1:$C$100)

Adjust the ranges to meet your needs.

HTH
Elkar


"Acey" wrote:

I have a set of data with countries and sectors and would like to tabulate
(sum) the data in one column of the rows meeting each of the two criteria. I
understand how to use sumif to collect data on either of the criteria
(summing data for country or sector in the example below), but don't know how
to collect the data meeting both criteria.
e.g. if the data set were the following:
country sector weight
china materials 1.4
china materials 3.0
china industrials 2.5
china technology 2.7
china industrials 1.3
brazil materials 4.3
brazil technology 2.1
russia materials 2.3
russia technology 3.1

I would like to find formulas which would help me fill out the following
array (x1:z3)
sector
country materials industrials technology
china x1 x2 x3
brazil y1 y2 y3
russia z1 z2 z3

Thank you in advance for your help!

acey

if you use microsoft office 2007 , you can useing sumifs formula,
if country name in calum A and sector in calum B and weight in calum C and
your table begin from row 12 you can write your formula
for example for x1 :
=SUMIFS($C$2:$C$10;$A$2:$A$10;A13;$B$2:$B$10;B12)
for example for x2 :
=SUMIFS($C$2:$C$10;$A$2:$A$10;A13;$B$2:$B$10;C12)
for example for y3 :
=SUMIFS($C$2:$C$10;$A$2:$A$10;A14;$B$2:$B$10;D12)

if is it ok press yes