Using wildcard with SUMPRODUCT (Excel 2003)

Topic

New Reply

I have the following SUMPRODUCT formula:

=SUMPRODUCT((Letters!$D$2:$D$3000>=BOM)*(Letters!$D$2:$D$3000<=EOM)*(LEFT(Letters!$C$2:$C$3000,14)="ACHLL1 Letters"),Letters!$E$2:$E$3000)

I would like to use this same type of formula somewhere else in my workbook and can't seem to modify it to meet my needs. How can I change this formula to look on the Letters!$C$2:$C$3000 and look for anything within that contains "can"? This would pull everything that I am looking for in this column (i.e. CANADIAN, CANADA, _Can).

Thanks for your help!

Reply | Quote

Replies

You can use the SEARCH function for this:

NOT(ISERROR(SEARCH(“can”,Letters!$C$2:$C$3000)))

SEARCH is not case-sensitive (FIND is its case-sensitive counterpart)

Reply | Quote

Thanks Hans! That worked like a charm!

Reply | Quote

Hans,

Is it possible to use this same type of formula to look for things in that column that do not have “can” in them?

Thanks again!

Reply | Quote

Simply omit the NOT( ) around the expression:

ISERROR(SEARCH(“can”,Letters!$C$2:$C$3000))

This returns TRUE if the SEARCH function results in an error, i.e. if the text “can” is not found.

Reply | Quote

Thanks again!!!

Reply | Quote