Here are a couple other options that work if Col_M contains numbers or "":

=SUMPRODUCT(($F$3:$F$1500={5,6,7})*($L$3:$L$1500< 0)*(--(0&$M$3:$M$1500)))

OR....if A1:A3 contain 5,6,7 respectively
=SUMPRODUCT(ISNUMBER(MATCH($F$3:$F$10,$A$1:$A$3,0) )*($L$3:$L$10<0)*(--(0&$M$3:$M$10)))

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

XL2002, WinXP


"Tester" wrote:

How do you do that? Thanks a million, I've changed "" to 0 and used
conditional formatting to leave the cell looking blank if the row isn't
populated.
Thanks to you all
Chris
"Ron Coderre" wrote in message
...
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