sumproduct not working

T

Tester

Hi this is my formula

=SUMPRODUCT(--($F$3:$F$1500=(OR(5,6,7))),--($L$3:$L$1500>0),$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
 
T

Teethless mama

Try this:

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

Ron Coderre

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$1500>0)*$M$3:$M$1500)

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

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

XL2002, WinXP
 
D

daddylonglegs

Another way is

=SUMPRODUCT(--ISNUMBER(MATCH$(F$3:$F$1500,{5,6,7},0)),--($L$3:$L$1500>0),$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$1500>0),$M$3:$M$1500)
 
B

Bob Phillips

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

--
HTH

Bob Phillips

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

Hi this is my formula

=SUMPRODUCT(--($F$3:$F$1500=(OR(5,6,7))),--($L$3:$L$1500>0),$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
 
T

Tester

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?
 
T

Teethless mama

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

Tester

Still returns the #VALUE error.
My formula in column F is
"=IF(D3<VAT!$N$26,-4,IF(D3>VAT!$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
 
R

Ron Coderre

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
 
T

Tester

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
 
R

Ron Coderre

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
 
Top