Wild cards in formulas for sumproducts

[LISAR]

Hi,
trying to do sumproducts with wildcard for search criteria and it doesn't seem to work.

This is a simpler version of what I am trying to do. I have multiple criteria for the sumproduct (like 4 or 5 columns), but have just put a simple example below. I have also shown that the wildcard works for the countif and sumif functions. Does anyone know what the wildcard is in a sumproduct.

Type in A1 = "cost to work"
Type in A2 = "the things we "
Type in A3 = "can costs everything"
Type in B1 = 10
Type in B2 = 10
Type in B3 = 20
Type in formula in A5 = =SUMIF(D3:D5,"*cost*",E3:E5)
= Result of 30
Type in formula in A6 = =COUNTIF(D3:D5,"*cost*")
= Result of 2
Type in formula in A7 = =SUMPRODUCT(--(D3:D5="*cost*"))
= Result of 0 which is INCORRECT it should be 2

Any ideas of what the wildcard is in SUMPRODUCT formula

 

[Brian from Maui]

Hi,
trying to do sumproducts with wildcard for search criteria and it doesn't seem to work.

This is a simpler version of what I am trying to do. I have multiple criteria for the sumproduct (like 4 or 5 columns), but have just put a simple example below. I have also shown that the wildcard works for the countif and sumif functions. Does anyone know what the wildcard is in a sumproduct.

Type in A1 = "cost to work"
Type in A2 = "the things we "
Type in A3 = "can costs everything"
Type in B1 = 10
Type in B2 = 10
Type in B3 = 20
Type in formula in A5 = =SUMIF(D3:D5,"*cost*",E3:E5)
= Result of 30
Type in formula in A6 = =COUNTIF(D3:D5,"*cost*")
= Result of 2
Type in formula in A7 = =SUMPRODUCT(--(D3:D5="*cost*"))
= Result of 0 which is INCORRECT it should be 2

Any ideas of what the wildcard is in SUMPRODUCT formula

For a single criteria SUMIF should suffice, for multi conditional counting/summing a generic approach,

=SUMPRODUCT(--(ISNUMBER(SEARCH("cost",Range))),--(Range="Criteria))