how do I use wild card in SUMPRODUCT formula?
 

Greetings!

I am using this formula and and it does not seem the syntax is correct:
=SUMPRODUCT(('Consolidated Report'!J5:J8941="NON ASCII; NON PRINTABLE;
NULLS; CARRIAGE CONTROL")*('Consolidated
Report'!A5:A8941<"Open")*('Consolidated Report'!LEFT(E5:E8941,6) = "R12741"))

Any help is appreciated!


Thanks

how do I use wild card in SUMPRODUCT formula?
 

Just tweak the LEFT part of it:
=SUMPRODUCT(('Consolidated Report'!J5:J8941="NON ASCII; NON PRINTABLE;
NULLS; CARRIAGE CONTROL")*('Consolidated
Report'!A5:A8941<"Open")*(LEFT('Consolidated Report'!E5:E8941,6)="R12741"))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---
"Foad" wrote:
I am using this formula and and it does not seem the syntax is correct:
=SUMPRODUCT(('Consolidated Report'!J5:J8941="NON ASCII; NON PRINTABLE;
NULLS; CARRIAGE CONTROL")*('Consolidated
Report'!A5:A8941<"Open")*('Consolidated Report'!LEFT(E5:E8941,6) = "R12741"))

how do I use wild card in SUMPRODUCT formula?
 

Hi,

First your title - SUMPRODUCT does not support wildcards, but then I don't
see any in your formula.
Everything is OK with respect to the formula as far as I can tell. However,
two points - I would construct the formula in the same sheet as all the
reference to make sure you there isn't a type in the sheet name portion. 2.
If you are trying to ask if the J range = one of the three "NON ASCII; NON
PRINTABLE; NULLS; CARRIAGE CONTROL" items this is not the correct way, if you
are trying to find all entries that have the entire string, it looks good to
me.
--
Thanks,
Shane Devenshire


"Foad" wrote:

Greetings!

I am using this formula and and it does not seem the syntax is correct:
=SUMPRODUCT(('Consolidated Report'!J5:J8941="NON ASCII; NON PRINTABLE;
NULLS; CARRIAGE CONTROL")*('Consolidated
Report'!A5:A8941<"Open")*('Consolidated Report'!LEFT(E5:E8941,6) = "R12741"))

Any help is appreciated!


Thanks

how do I use wild card in SUMPRODUCT formula?
 

Everything is OK with respect to the formula as far as I can tell...

Think the LEFT part of the OP's formula just needs correction
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000, Files:362, Subscribers:62
xdemechanik
---

how do I use wild card in SUMPRODUCT formula?
 

Yes,

I read your response too quickly the first time and thought you meant the
Left portion of the formula, as in beginning. Not the Left function. You
are correct.

--
Thanks,
Shane Devenshire


"Max" wrote:

Everything is OK with respect to the formula as far as I can tell...

Think the LEFT part of the OP's formula just needs correction
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000, Files:362, Subscribers:62
xdemechanik
---