Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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")) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
wild card?? formula question please | Excel Discussion (Misc queries) | |||
Using wild card in IF formula | Excel Discussion (Misc queries) | |||
wild card in sumproduct | Excel Worksheet Functions | |||
sumproduct with a search and wild card | Excel Discussion (Misc queries) | |||
wild card -- help with formula | Excel Discussion (Misc queries) |