Sumproduct returning #NUM!

[wal50]

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

 

[PeterAtherton]

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

 

[JulieD]

Hi

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

additionally, the ranges must be the same size

Cheers
JulieD

 

[Pierre Leclerc]

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
(e-mail address removed)

 

[wal50]

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.