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

one way is to use an array function entered with Ctrl + Shift + Enter
try =SUM(IF((details!Region="James")*(details!Contract ="n/g"),details!amount))

Excel will enter curly brackets around the formula if you have entered it
properly

Regards
Peter Atherton

"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

Hi

try
=SUMPRODUCT((Detail!Region="james")*(detail!Contra ct="n/g")*(detail!Amount))
additionally,
you left the brackets off around ALL the parameters

additionally, the ranges must be the same size

Cheers
JulieD


"wal50" wrote in message
...
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

Hi

In the range that you are summing, there is text or spaces. It is the
most common error messge when using SUMPRODUCT.

For a complete tutorial on SUMPRODUCT and other functions and formula
see the site below

http://www.excel-vba.com


Pierre Leclerc
http://www.excel-vba.com

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