Summing multiple sheets with a "contains" formula

[Excel N00b]

Hey guys. I apologize in advance if this is answered in the forums - very happy if I'm directed to a previous response. I was not able to find after extensive searching, but that may be due to my searching abilities!
I am working on an audit project with multiple sheets and the input is very similar, but some of the bookkeeping has extra entries after the company names. I am using a function: =SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A3&"'!A2:A10"),C2,INDIRECT("'"&A2:A3&"'!B2:B10")))
but this will only work if the name is EXACTLY the same on each sheet. Is there a way to =sumproduct if the name contains a portion of the search term on the 1st sheet? Example attached. Trying to sum all sheets for 'Sample Co 1' but 2nd sheet has extra characters after this name.

The time savings would be incredible if I do not have to manually change the company names to match! Thank you in advance.

[:) Sixthsense :)]

Simple, just add a wildcard (i.e.) * which means any character and any length of character. So adding * in the end of the Criteria will do that trick to you


=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$2:$A$3&"'!A2:A10"),C2&"*",INDIRECT("'"&$A$2:$A$3&"'!B2:B10")))

[Excel N00b]

sixthsense - thanks a ton! my screenname is also my experience level LOL