Sumproduct

[kpro]

I am working on a quoting sheet for my company, (trying to impress the new boss), and have figured out how to do the dependent data validation, which is working perfectly. But I need to populate a cost of the item that ends up being selected by the data validation. (It will match a specific item at the end, each option will have its own separate cell) When I enter a Sumproduct formula that I have used on a different project working with the same type of set up, I am getting a 0. I have checked the cell format, set it to general, then number, then back to general and I canNOT get it to populate. If anyone can direct me on what I am doing wrong, that would be amazing.

Thank you!!

Formula being used:
=IFERROR(SUMPRODUCT((Sheet2!$A$3:$A$14=Sheet1!A3)*(Sheet2!$B$3:$B$14=Sheet1!B3)*(Sheet2!$C$3:$C$14=Sheet1!C3)*(Sheet2!$D$3:$D$14=Sheet1!D3)*(Sheet2!$F3:$F14=Sheet1!E3)*(Sheet2!$H$3:$H$14)), "")

The salesman will select all options in row 3 - they will very - i am looking for the cost to populate in f3(that is where the formula is)

Selections are being made on "Sheet2"

Here is the data table on "Sheet1"

 

[Fluff]

It looks as though you might have the sheet references the wrong way round, judging by your description.

 

[kpro]

It looks as though you might have the sheet references the wrong way round, judging by your description.

I just checked myself, I typed it wrong in here, thank you for that. My Sheet 2 is where I have the data table. Sheet 1 is my salesmen's selection sheet.

 

[Fluff]

Ok, in B3 you have TREX, but thee only value visible on the other sheet is TREK.

 

[kpro]

Ok, in B3 you have TREX, but thee only value visible on the other sheet is TREK.

OH MY GOSH - I HAVE BEEN ON THIS FOR 3 DAYS LOL you are so wonderful, thank you so stinkin much!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ExceLoki]

Selections are being made on "Sheet2"

Here is the data table on "Sheet1"

if your data table is on sheet 1 and your selections being made are on sheet 2, looks like you have them backwards in your formula

=IFERROR(SUMPRODUCT((Sheet1!$A$3:$A$14=Sheet2!A3)*(Sheet1!$B$3:$B$14=Sheet2!B3)*(Sheet1!$C$3:$C$14=Sheet2!C3)*(Sheet1!$D$3:$D$14=Sheet2!D3)*(Sheet1!$F3:$F14=Sheet2!E3)*(Sheet1!$H$3:$H$14)), "")

 

[Fluff]

Which is what I said 1/2 an hour ago.

 

[ExceLoki]

Which is what I said 1/2 an hour ago.

didnt see that post