SUMPRODUCT with blank rows returns a #value result

[Rajus]

I have used SUMPRODUCT in the past with multiple conditions and it worked good. Unfortunately, not this time.

Here's my scenario

Column A - Product Name
Row 1 - Year # stored as text
Row 2 - Month Name

This table contains sales figures by product for the specific period.

I used SUMPRODUCT(--(Sales!A6:A237=Summary!A7),--(Sales!3:3=Summary!C2),--(Sales!3:3=Summary!C3),(Sales!C6:W237)) to identify the sales figure based on the month, year and product name in a summary sheet.

Returns a #Value. The table does have blank rows in them.

What am I missing? Any help would be greatly appreciated.

Thanks a ton!

 

[DonkeyOte]

Your ranges are not of the same dimensions

ie in some cases you're looking at entire rows whereas in others you're looking at a selection of rows in a given column -- this will not work.