Return cost with 2 criteria

[Amber]

I have an excel spreadsheet with 2 sheets. I need to return a value based
off of 2 criteria.

Sample Data: First Sheet
A B C
3592 6120 42.6
3691 6120 39.71
3700 6120 47.17
3709 6120 43.6
3592 X911 26.5
3691 X911 26.5
3700 X911 25.75
3709 X911 25.75

Sample Data: Second Sheet
A B C
3691 6120 ?
3700 6120 ?
3709 6120 ?
3691 X911 ?
3700 X911 ?
3709 X911 ?

I could do this manually but I have over 400 number for column A and 1900
for column B.

I would like say. If the number in A on the second sheet is 3691 and x911,
What is the price?

Please assist.
Thanks, AP

 

[Bernie Deitrick]

Amber,

In cell C2 of the second sheet

=SUMPRODUCT(('First Sheet'!$A$1:$A$10000=A2)*('First Sheet'!$B$1:$B$10000=B2)*'First
Sheet'!$C$1:$C$10000)

and then copy down.

HTH,
Bernie
MS Excel MVP

 

[Amber]

I performed the calculation but received. #VALUE!

=SUMPRODUCT((Contracts!$A$1:$A$12900=A5)*(Contracts!$B$1:$B$12900=D1)*Contracts!$C$1:$C$12900)
This is the exact formula I used based off of my information. What am I
doing wrong?

I did hit enter after the formula. Could that be my problem?

 

[Bernie Deitrick]

Amber,

Your formula worked fine for me.

If Contracts!C1:C12900 has any text in any cell, then you will get the #VALUE! error. Probably you
have headers in row 1, so shorten the range to exclude row 1:

=SUMPRODUCT((Contracts!$A$2:$A$12900=A5)*(Contracts!$B$2:$B$12900=D1)*Contracts!$C$2:$C$12900)



HTH,
Bernie
MS Excel MVP

 

[Amber]

I believe that worked. Thanks a bunch for the info.

One more quick question: When I copy formulas that reference cell numbers,
the numbers add to the previous and of course changes the formula. Is there
a way to copy formulas such as the one I am using for this without changing
it? If not, it would take just as long to change the formula for each line ad
looking it up manually.

 

[Bernie Deitrick]

D$1 freezes the row, $D1 freezes the column, $D$1 freezes the cell.

HTH,
Bernie
MS Excel MVP