excelexcel-formulasumproduct

Sumproduct Wildcard issue

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.

enter image description here

Many thanks, Alan.

Solution

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

    enter image description here

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

    Another alternative way:

    enter image description here

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