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

Try this:

=SUMPRODUCT(($F$3:$F$1500={5,6,7})*($L$3:$L$15000 )*($M$3:$M$1500))


"Tester" wrote:

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

I think in this situation you'd need to use this variation of the SUMPRODUCT
function:

=SUMPRODUCT(($F$3:$F$1500={5,6,7})*($L$3:$L$15000 )*$M$3:$M$1500)

Note the curly braces around the 5,6,7 array

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

XL2002, WinXP


"Tester" wrote:

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

Another way is

=SUMPRODUCT(--ISNUMBER(MATCH$(F$3:$F$1500,{5,6,7},0)),--($L$3:$L$15000),$M$3:$M$1500)

This syntax allows you to replace {5,6,7} with a range of cells containing
your possible match values, e.g., especially useful if there are large
numbers of possible or if they're variable

=SUMPRODUCT(--ISNUMBER(MATCH$(F$3:$F$1500,X1:X3,0)),--($L$3:$L$15000),$M$3:$M$1500)



"Ron Coderre" wrote:

I think in this situation you'd need to use this variation of the SUMPRODUCT
function:

=SUMPRODUCT(($F$3:$F$1500={5,6,7})*($L$3:$L$15000 )*$M$3:$M$1500)

Note the curly braces around the 5,6,7 array

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

XL2002, WinXP


"Tester" wrote:

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

=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

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

=SUMPRODUCT(ISNUMBER($F$3:$F$1500)*($F$3:$F$1500={ 5,6,7})*($L$3:$L$15000)*($M$3:$M$1500))


"Tester" wrote:

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

Still returns the #VALUE error.
My formula in column F is
"=IF(D3<VAT!$N$26,-4,IF(D3VAT!$N$26,MONTH(D3),0))"
Column D is a date in format dd/mm/yyyy entered by user. VAT!$N$26 is a
number equivalent of a date ie 38838
If I reduce the length of the column ranges from 1500 to 900 so that they
only include rows with data entered it works, but obviously i need to
prepare the sheet for use from new, i.e. with no data in at least some of
the rows. Could I add a rider to limit the row length to only completed rows
perhaps?
Thanks again

"Teethless mama" wrote in message
...
=SUMPRODUCT(ISNUMBER($F$3:$F$1500)*($F$3:$F$1500={ 5,6,7})*($L$3:$L$15000)*($M$3:$M$1500))


"Tester" wrote:

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

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

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

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