Trouble Shooting SumProduct Formula

Hello,
The following formula was provided in this forum and was applied with success in an excel model I developed. However, in a specific look up, it is not working and returning the value:#DIV/0!

=SUMPRODUCT(--('Discharge Lookup'!$A$2:$A$121='Summary Model_Partner Look'!$B$3),--('Discharge Lookup'!$B$2:$B$121='Summary Model_Partner Look'!A9),'Discharge Lookup'!$F$2:$F$121)

Array 1: Looking up Text Field in "Discharge Lookup" based on drop list for same field in Summary Model_Partner Look

Array 2: Same as Array 1

Result: Providing data field look up based on both arrays.

Not sure why this is not working. Does return value have to be a non-formula number? Thanks for your assistance.

Is that error present in any of those ranges?

Originally Posted by rgold

I am not able to tell

Simply navigate to Discharge Lookup sheet and look for #DIV/0! error values in ranges A2:A121, B2:B121 and F2:F121.

Sumproduct multiplies.. so you can't get Divide by zero error since you are not dividing... the only way I can think that you are getting this is that there that error exists in one or more of the cells in the range...

Can we see the sheet or representative fac simile.

No need to thank me I was just reiterating NBVC's point