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.

 

[AlphaFrog]

Try something like this

=SUMPRODUCT(--(TEXT(G50,"@")=RIGHT(SAP!$G2:G500,4)),--(TEXT(SAP!$A$2:$A$500,"mmm-yy")=TEXT(I1,"mmm-yy")), SAP!$I$2:$I$500)

 

[njimack]

All 3 ranges need to be of the same size (one goes to row 500, the others to row 200). Also, your first text function is missing the second arguement.
If you're testing for cells in column G that contain the value in G50, it would be something like this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("???????"&$G50,SAP!$G2:G200)),--(SAP!$A$2:$A$200=I1),(SAP!$I$2:$I$200))

 

[jwah]

Hi AlphaFrog,

Perfect, some very minor adjustments with locking the range and its ready for copying.

Cheers jwah@nz

Try something like this

=SUMPRODUCT(--(TEXT(G50,"@")=RIGHT(SAP!$G2:G500,4)),--(TEXT(SAP!$A$2:$A$500,"mmm-yy")=TEXT(I1,"mmm-yy")), SAP!$I$2:$I$500)

 

[jwah]

What does the format '@' in the TEXT command do ?

 

[jwah]

Sorry one more....

I have (and it works)

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

Bit I also want to clone and perform a wildcard search across SAP!$G$2:$G$500

instead of the right four chars? how to match on *F47* in G2:G500?

 

[Aladin Akyurek]

Sorry one more....

I have (and it works)

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

Bit I also want to clone and perform a wildcard search across SAP!$G$2:$G$500

instead of the right four chars? how to match on *F47* in G2:G500?

This can be shortened a bit:

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

Not sure what "clone" means in: "I also want to clone and perform a wildcard search across SAP!$G$2:$G$500", but try one of:

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

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

 

[jwah]

Sorry the word 'clone' was misleading.

If F47 is defg I want to find all the rows from G2: G500 where defg appears.

e.g. abcdefghi or defghijk

but

efghi would fail.

and for the rows that match SUM column I.

 

[Aladin Akyurek]

Sorry the word 'clone' was misleading.

If F47 is defg I want to find all the rows from G2: G500 where defg appears.

e.g. abcdefghi or defghijk

but

efghi would fail.

and for the rows that match SUM column I.

Is the date condition still required?

 

[jwah]

Yes re date.