SUMPRODUCT using vertical and horizontal matches

[mssbass]

I'm trying to build a formula in my rates tab (see below) to show the minimum price of the unit found. I've started trying to build the SUMPRODUCT statement but getting a value error (see rates tab below formula bar). I noticed the dates on the comps tab were string rather than date so I added a row above to edit the dates to a datevalue (but even changing them to a date and adjusting the name range doesn't fix the issue). The SUMPRODUCT for this unit/date should be $685 and the minimum is $95 (see comps tab highlighed in yellow). Can someone help me with this formula?

I was unable to add a mini sheet due to company policy popup

 

[Alex Blakenburg]

Would this work for you ?
Assumes you are pulling your data from the sheet comps.

=SUMIFS(
     INDEX(comps!$A$1:$I$20,
                  0,
                  MATCH($A2,comps!$A$1:$I$1,0)),
     comps!$A$1:$A$20,$F2)

You might want to update your profile to show which version of Excel you are using, the solution often varies based on the version.