Thank you all for your help.
I had the range names for the entire column in each case so they were all
the same size. When I used the actual row range and didn't count the header
row (A2:A332), and made sure they were all the same size with no blanks,
everything worked. Unfortunately, I did all 3 things at the same time so am
not sure which one fixed it.

"wal50" wrote:

Using:
=SUMPRODUCT(Detail!Region="james")*(detail!Contrac t="n/g")*(detail!Amount)
returns #NUM! Could this be because some cells in Amount range are blank? I
appreciate the help