Forum Discussion
SUMPRODUCT with partial match
In column A I have a list of product descriptions. In column B I have the quantity of items and in column C I have the values of the items. I would like to multiply B and C where a partial description matches in column A. Wildcards do not work with SUMPRODUCT.
Example of a product description in column A, QTY in B and value in c
BASEPAINT ACRYLIC MATT PASTEL 5L 10 $27.00
BASEPAINT PVA MATT DEEP 5L 5 $18.00
I tried the following formula which did not work:
=SUMPRODUCT(IF(COUNTIF(A:A="*MATT*5L*"),1,0),B:B,C:C)
The answer should be = 10x$27 = 5x$18 = $360
=SUMPRODUCT(ISNUMBER(SEARCH("MATT",A1:A10))*ISNUMBER(SEARCH("5L",A1:A10))*(B1:B10)*(C1:C10))
This formula works in my sheet.
