Wild Card in SUMPRODUCT formula search

Bravo2003

Active Member
Joined
Apr 1, 2010
Messages
260
=SUMPRODUCT(--('2019 Sales'!$CR$1:$CR$1055<>"IND")*--('2019 Sales'!$D$1:$D$1055=$A$2)*--('2019 Sales'!$B$16:$BY$16=BX$1)*--('2019 Sales'!$B$17:$BY$17=$A3),'2019 Sales'!$B$1:$BY$1055)

Using the above SUMPRODUCT formula - where I have "IND"
aim is to sum everything excluding any matches to IND, but I have IND-xx (various xx references - eg IND-JC, etc)

I tried using the WildCard * - <>"IND*"
But this doesn't exclude anything.

Am i using the right format? or is there any way to ask for a wild card search?

Or will I have to revert to adding a new column along side the IND-* column to remove the suffixes?
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
=SUMPRODUCT(--(ISERROR(SEARCH("IND",'2019 Sales'!$CR$1:$CR$1055))),--....
 

Watch MrExcel Video

Forum statistics

Threads
1,109,494
Messages
5,529,192
Members
409,857
Latest member
KailuaTown
Top