Sum based on criteria in rows and columns
 

I have a sheet of data which runs horizontally containing 12 months of data.
I need a formula which will lookup criteria horizontally as well as
vertically and return the summed value of all occurences. So I would like it
to sum all instances of FR and abc when row 1 equals Nov-05 and row 2 equals
OOH.

A B C D
1 Nov-05 Nov-05
2 REP CUST OOH VALUE
3 FR abc 100 300
4 FR abc 200 100
5 JPZ def 300 200
6 JPZ def 100 300
7 JPZ ghi 200 100
8 JPZ ghi 300 200

Thank you in advance,

Esther

Sum based on criteria in rows and columns
 

=SUMPRODUCT((A2:A20="FR")*(B2:B20="abc")*(C1:Z1=--"2005-11-05")*(C2:Z2="OOH"
),C2:Z20)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"EstherJ" wrote in message
...
I have a sheet of data which runs horizontally containing 12 months of
data.
I need a formula which will lookup criteria horizontally as well as
vertically and return the summed value of all occurences. So I would like
it
to sum all instances of FR and abc when row 1 equals Nov-05 and row 2
equals
OOH.

A B C D
1 Nov-05 Nov-05
2 REP CUST OOH VALUE
3 FR abc 100 300
4 FR abc 200 100
5 JPZ def 300 200
6 JPZ def 100 300
7 JPZ ghi 200 100
8 JPZ ghi 300 200

Thank you in advance,

Esther