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.
>Solution :
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))