Left as a condition in SUMPRODUCT

[Kigol]

This function works fine until I insert the LEFT condition. Any ideas
on how to make it work? I tried entering it as an array as well and
nothing.

Works:
=SUMPRODUCT((Summary!M7:M65536=1)*(Summary!K7:K65536=1),(Summary!
J7:J65536))

Doesn't work:
=SUMPRODUCT((Summary!M7:M65536=1)*(LEFT(Summary!K7:K65536,1)=1),
(Summary!J7:J65536))

 

[JE McGimpsey]

LEFT() returns a string, which you're comparing to a number. Try:

=SUMPRODUCT(--(Summary!M7:M65536=1),--(LEFT(Summary!K7:K65536,1)="1"),
Summary!J7:J65536)

 

[Guest]

Left works on strings and sumproducts is works on numbers. You need to
convert. Not sure what you data is.

value(Text) to convert text to number

text(number,"general") to convert number ot text

 

[Guest]

Left returns a string and not a value. Put the 1 in quotes...

=SUMPRODUCT((Summary!M7:M65536=1)*(LEFT(Summary!K7:K65536,1)="1"),
(Summary!J7:J65536))

 

[Dave Peterson]

=left() returns a string.

....*(LEFT(Summary!K7:K65536,1)="1"), ...

 

[Kigol]

Yes of course that makes sense. Thank you. Also, I have tried a few
times in the past but to make sure. You cannot use OR or AND in
SUMPRODUCT functions correct? You would just have to SUM two different
SUMPRODUCT functions with different conditions?

 

[Dave Peterson]

You can add more requirements with something like:
=sumproduct(--(a1:a10="hi"),--(b1:b10="there"),c1:c10)
(And-ing column A with column B)

You could use:
=sumproduct(--(((a1:a10="hi")+(b1:b10="there"))>0),c1:c10)
(Or-ing column A with column B)

This was recently posted by Peo Sjoblom:
One way
=SUMPRODUCT(--(A2:A10="N"),--((B2:B10=4)+(B2:B10=5)>0))
another
=SUMPRODUCT(((A2:A10="N")*(B2:B10={4,5})))

======
Can you figure out what it does? <bg> (answer below...)

Those people who answer lots of questions in .worksheet.functions are pretty
darn smart.

If you have followup questions, you may want to post in that newsgroup with the
details.

..scroll down for the answer
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
From the OP: