how do I use wild card in SUMPRODUCT formula?

[Foad]

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

 

[Max]

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

 

[ShaneDevenshire]

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.

 

[Max]

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

 

[ShaneDevenshire]

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.