SumProduct error

[jwah]

Here goes:

I have two criteria items in order to sum up some numerical values.

In the sheet 'SAP' in column A I have a date which month value needs to match; and in Column G, a string in the format ???????1234 whereby only the 1234 (last four chars) need to match a value in a cell reference (G50).
For where in column A the month matches, AND column G the last 4 characters match I need to SUM column I.

This is what I have so far:

=SUMPRODUCT(G50=TEXT(SAP!$G2:G500,"???????"&$G50&"*"),--(TEXT(SAP!$A$2:$A$200,"mmm-yy")=TEXT(I1,"mmm-yy")),SAP!$I$2:$I$200)

Where G50 is my 1st reference and I1 is my 2nd reference; but cell still returns '#VALUE'.

Help welcome.

 

[Aladin Akyurek]

Yes re date.

Try...

=SUMPRODUCT(
    --ISNUMBER(SEARCH($F47,SAP!$G$2:$G$500)),
    --(TEXT(SAP!$A$2:$A$500,"mmm-yy")=TEXT(I$1,"mmm-yy")),
    SAP!$I$2:$I$500)