hi Annie,


there are many tutorials for sumproduct on the net,
i hope you enjoy reading this:

http://www.exceluser.com/explore/sumproduct_11.htm
http://www.meadinkent.co.uk/xlsumproduct.htm
http://office.microsoft.com/en-us/ex...005209293.aspx

--
isabelle



Le 2012-06-11 10:17, Annieeee a écrit :
Hi Isabelle,

Thanks for your response but I don't understand the formula you used.
Could you please explain the logic behind the formula? If I can
understand it, I can adjust on my sheet.

Here's the example of my excel again:
SHEET 1
.........A..........B..........C..........D
1.......STATE....HOT.......COLD....WARM (<<THIS IS THE HEADER)
2........CA
3........FL
4........TX

SHEET 2
.........A............B................C......... .
1.......STATE..CLIMATE....TOTAL CITIES (<<THIS IS THE HEADER)
2.........CA.......COLD.............5
3.........FL........HOT..............6
4.........TX........HOT.............15
5.........CA.......WARM............2
6.........CA.......HOT...............7
7.........FL.......COLD..............3
8.........TX.......WARM.............8

I need a formula where SHEET 1 is filled as follows:
.........A..........B..........C..........D
1.......STATE....HOT.....COLD....WARM (<<THIS IS THE HEADER)
2........CA.........7...........5.........2....
3........FL..........6...........3.........0...
4........TX.........15..........0.........8...