I've seen multiple posts on this topic but I couldn't understand any of them (either I am just too new to this or it wasn't what I am looking for).

Someone help!

Example of my excel:
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

Basically, I want to look up the STATE and the CLIMATE and return the values onto Sheet 1. Is this possible? Feedback is certainly appreciated

hi Annie,

in SHEET 1 cellule B2:

=SUMPRODUCT(--(Feuil2!$A$2:$A$10=$A2)*(Feuil2!$B$2:$B$10=B$1)*(F euil2!$C$2:$C$10))

copy B2 to right and down

--
isabelle



Le 2012-06-08 12:31, Annieeee a écrit :
I've seen multiple posts on this topic but I couldn't understand any of
them (either I am just too new to this or it wasn't what I am looking
for).

Someone help!

Example of my excel:
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

Basically, I want to look up the STATE and the CLIMATE and return the
values onto Sheet 1. Is this possible? Feedback is certainly appreciated

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...

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...