Could it be that some of the Col_M "blanks" are actually "" values returned
by formulas?
As in: =IF(some_formula,"",a_number)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Tester" wrote:

Thank you all. I have tried each answer in turn and response 1, 2 and 4
return the #VALUE error. Daddylonglegs returned a value which when I checked
it with a data sort was short by 378.07. Wherever possible I try to use as
small a formula as I can and appreciating that 3 of you made the effort, I
have tried to understand why the error value was returned. Correct syntax
excepted my first thought was i had an error value somewhere in one of the
column ranges but I don't see one. Then i wondered if it is because the
middle range (L3:L1500) has a lot of blank cells (no formulae, no value)? I
hovered over trace error and all 3 say that "a value used in the formula is
of the wrong data type" My column F was general format so changed it to
number to no avail and the other columns are numbers also. I have seen that
column F is not (and cannot be) sorted so is that the problem. Also where no
data is currently entered from row 920 downwards the cell in column F
shows -4 but when i shortened the range to only include numbers above 1 it
made no difference. Finally, I have realised that sometimes the value in
column L could be a negative number so would it be ok to use <0? I was
trying to catch the numbers in column M where there was a number in L.Any
ideas please?

"Bob Phillips" wrote in message
...
=SUMPRODUCT((OR($F$3:$F$1500={5,6,7}))*($L$3:$L$15 000)*$M$3:$M$1500)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Tester" wrote in message
...
Hi this is my formula

=SUMPRODUCT(--($F$3:$F$1500=(OR(5,6,7))),--($L$3:$L$15000),$M$3:$M$1500)

but it returns zero value and i'm guessing its the OR function, but how
can
I work around this please?

TIA
Chris