Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I have a problem with these two formulas and a wild card
I need a wild card for "Import". List of data has "IMPORT BREAKBULK" and
"IMPORT BROKERAGE", Thanks The following do not work correctly. =SUMIF('All Divisions'!$U$2:$U$3321,"IMPORT*",'All Divisions'!$S$2:$S$3321) =SUMPRODUCT(--('All Divisions'!$U$2:$U$3321="IMPORT*"),--('All Divisions'!$J$2:$J$3321=30),--('All Divisions'!$S$2:$S$3321)) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
I have a problem with these two formulas and a wild card
Your =sumif() formula should work ok.
Maybe the values in S2:S3321 aren't really numbers--maybe they are text that look like numbers. pgarcia wrote: I need a wild card for "Import". List of data has "IMPORT BREAKBULK" and "IMPORT BROKERAGE", Thanks The following do not work correctly. =SUMIF('All Divisions'!$U$2:$U$3321,"IMPORT*",'All Divisions'!$S$2:$S$3321) =SUMPRODUCT(--('All Divisions'!$U$2:$U$3321="IMPORT*"),--('All Divisions'!$J$2:$J$3321=30),--('All Divisions'!$S$2:$S$3321)) -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
I have a problem with these two formulas and a wild card
hi
try your wild card using this syntax... SUMIF('All Divisions'!$U$2:$U$3321,"IMPORT" & "*",'All Divisions'!$S$2:$S$3321) ie add the apersand concatinator and enclose the "star" in quotes also. works in xp regards FSt1 "pgarcia" wrote: I need a wild card for "Import". List of data has "IMPORT BREAKBULK" and "IMPORT BROKERAGE", Thanks The following do not work correctly. =SUMIF('All Divisions'!$U$2:$U$3321,"IMPORT*",'All Divisions'!$S$2:$S$3321) =SUMPRODUCT(--('All Divisions'!$U$2:$U$3321="IMPORT*"),--('All Divisions'!$J$2:$J$3321=30),--('All Divisions'!$S$2:$S$3321)) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
I have a problem with these two formulas and a wild card
As Dave noted, your col S could be/contain text numbers
Think you could try this pair which should work fine: 1. =SUMPRODUCT((ISNUMBER(SEARCH("IMPORT",'All Divisions'!$U$2:$U$3321)))*'All Divisions'!$S$2:$S$3321) 2. =SUMPRODUCT((ISNUMBER(SEARCH("IMPORT",'All Divisions'!$U$2:$U$3321)))*('All Divisions'!$J$2:$J$3321=30)*'All Divisions'!$S$2:$S$3321) Replace SEARCH with FIND if you need it to be a stricter, case sensitive search. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "pgarcia" wrote: I need a wild card for "Import". List of data has "IMPORT BREAKBULK" and "IMPORT BROKERAGE", Thanks The following do not work correctly. 1. =SUMIF('All Divisions'!$U$2:$U$3321,"IMPORT*",'All Divisions'!$S$2:$S$3321) 2. =SUMPRODUCT(--('All Divisions'!$U$2:$U$3321="IMPORT*"),--('All Divisions'!$J$2:$J$3321=30),--('All Divisions'!$S$2:$S$3321)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
wild card in sumproduct | Excel Worksheet Functions | |||
Wild Card Search | Excel Worksheet Functions | |||
wild card -- help with formula | Excel Discussion (Misc queries) | |||
Wild card * | Excel Worksheet Functions | |||
Using wild card characters in array formulas | Excel Worksheet Functions |