SumProduct: Find value at intersection

[gheyman]

I have Peoples Names down A starting in Row 2 down to 10 and Product ID in Row 1 starting in B thru H. My Values are in B2:H10

SumProduct((Indirect("SHeet1!$A$2:$A$10")=A1)*(Indirect("SHeet1!$B$1:$H$1")=B1)*(Indirect("SHeet1!$B$2:$H$10"))

Gives me an error

What am I doing wrong?

Thanks

 

[gheyman]

I'm pulling data into a form.

 

[Joe4]

An Index with two Match formula gets me what I need in this instance.

But here is the formula I have in another workbook where I used the Sumproduct. This formula works

=SUMPRODUCT((INDIRECT("Cost_Data!$A$4:$A$1000")=$C$3)*(INDIRECT("Cost_Data!$D$4:$D$1000")=$D6)*(INDIRECT("Cost_Data!$I$3:$O$3")=I$4)*INDIRECT("Cost_Data!$I$4:$O$1000"))

OK, I am not quite sure I understand how that is working (especially without access to see what that data all looks like). I am guessing that you probably had someone write that for you? If so, that person might be your best bet in adapting the code to work for you.

I am still not quite clear on what this output you want looks like, whether you are wanting to return a single value, a subset of data, etc.
Without having a full understanding of exactly what you are after, I don't think there is much more I can offer.
If you can post images of example data and what your expected results should look like, that would go a long way.

 

[Tetra201]

@ gheyman:

The formula from Post # 1 is missing a closing bracket -- otherwise it works OK.

What I don't understand is why you need the INDIRECTs.

Will the following work for you?

=SUMPRODUCT((Sheet1!$A$2:$A$10=A1)*(Sheet1!$B$1:$H$1=B1),Sheet1!$B$2:$H$10)