Help with Sumproduct(Sumif(Indirect

[IainG]

Hi,

Can anyone tell me if its possible to use the LEFT() function or an equivalent function within this formula,

=SUMPRODUCT(SUMIF(INDIRECT("'"&$E8&"'!$F$6:$F$20"),$F8,INDIRECT("'"&$E8&"'!A$6:A$20")))

The INDIRECT(ref_text) is working perfectly when it finds a complete match, but I want it to also work if the criteria matches say the first 14 characters.

Any help will be much appreciated and will probably add years to my life!:banghead:

 

[NBVC]

Do you mean?

=SUMPRODUCT(SUMIF(INDIRECT("'"&$E8&"'!$F$6:$F$20"),LEFT($F8,14),INDIRECT("'"&$E8&"'!A$6:A$20")))

or

=SUMPRODUCT(SUMIF(INDIRECT("'"&$E8&"'!$F$6:$F$20"),$F8&"*",INDIRECT("'"&$E8&"'!A$6:A$20")))

 

[IainG]

NBVC,

The 2nd option worked perfectly!
So obvious when you see it.

Thanks You.

 

[NBVC]

I think it should also work as:

=SUMIF(INDIRECT("'"&$E8&"'!$F$6:$F$20") ,$F8&"*",INDIRECT("'"&$E8&"'!A$6:A$20"))

No need for the SUMPRODUCT.