1

I have the SUMPRODUCT working with hardcoded values however I want to use a wild card for the B clomun in my example.

Here is my data

+----------+----------+-----------+
| A COLUMN | B COLUMN | C COLUMN  |
+----------+----------+-----------+
| Status   | Fruit    | Quanitity |
|          |          |           |
| Fresh    | Apple    | 6         |
|          |          |           |
| Fresh    | Apricot  | 7         |
|          |          |           |
| Stale    | Apple    | 4         |
+----------+----------+-----------+

I would like to match Fresh, AP* and then sum the matches form Column C.

I have the following

=SUMPRODUCT(--($B$2:$B$840="AP*"),--($A$2:$A$840="Fresh"),$C$2:$C$840)

Working code with the Wildcard but the count is off

=SUMPRODUCT(ISNUMBER(SEARCH"AP",$B$2:$B$840,1))*($A$2:$A$840="Fresh")*($C$2:$C$840))
JA1
  • 538
  • 2
  • 7
  • 21

1 Answers1

3

The SUMPRODUCT() function does not support wildcards within an array-type expression. The same result can be achieved with:

=SUMPRODUCT((A2:A1000="Fresh")*(LEFT(B2:B1000,2)="Ap")*(C2:C1000))

enter image description here

Gary's Student
  • 95,722
  • 10
  • 59
  • 99