Wild cards in formulas for sumproducts

LISAR

Board Regular
Joined
Aug 10, 2002
Messages
85
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
 

Some videos you may like

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
Joined
Feb 16, 2002
Messages
8,458
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,114,060
Messages
5,545,761
Members
410,704
Latest member
Cobber2008
Top