I have a problem with these two formulas and a wild card

[pgarcia]

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]

Your =sumif() formula should work ok.

Maybe the values in S2:S3321 aren't really numbers--maybe they are text that
look like numbers.

 

[FSt1]

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

 

[Max]

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.