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:K655 36=1),(Summary!
J7:J65536))

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

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)


In article .com,
Kigol wrote:

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:K655 36=1),(Summary!
J7:J65536))

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

=left() returns a string.

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



Kigol wrote:

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:K655 36=1),(Summary!
J7:J65536))

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

--

Dave Peterson

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?

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:
What I am trying to do is to count the number of "N"s in a column that
happen to be in the same row with another column that shows a 4 or 5.



Kigol wrote:

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

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


"Kigol" wrote:

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:K655 36=1),(Summary!
J7:J65536))

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

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))
--
HTH...

Jim Thomlinson


"Kigol" wrote:

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:K655 36=1),(Summary!
J7:J65536))

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