Sumproduct Wildcard issue

Question

I have the following formula in cell R5:

=SUMPRODUCT((I4:N28)*(B4:B28=R3)*(I1:N1="*"&R4&"*"))

I would expect it to return 120 but returns 0. It transpires I can not use wildcard in sumproduct() like I typically would with sumif(). Any help on incorporating a wildcard into sumproduct() is appreciated.

Many thanks, Alan.

Answer 1

Try using the following formula which uses SEARCH() Function.

---

=SUMPRODUCT((ISNUMBER(SEARCH(R4,I3:N3)))*(B4:B19=R3)*(I4:N19))

---

Another alternative way:

---

=SUMPRODUCT((1-ISERR(SEARCH(R4&"*",I3:N3)))*(R3=B4:B19)*(I4:N19))

---