# Wild cards in formulas for sumproducts

#### LISAR

##### Board Regular
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

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Brian from Maui

##### MrExcel MVP
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))

Replies
7
Views
59
Replies
4
Views
200
Replies
14
Views
258
Replies
0
Views
45
Replies
7
Views
71