Sumproduct for partial text

J

Jasmine

I am using the following Sumproduct command and it has been working great.

=SUMPRODUCT(--('[Past Dues Aug.xls]Sheet1'!$A$1:$A$5000="Tom
Bonnette"),'[Past Dues Aug.xls]Sheet1'!$B$1:$B$5000)

However, the Past Dues sheet I am pulling information from, the names in
column A have been changed. So now the example above looks like Tom Bonnette
(24). There is a likely hood that the 24 could change. How can I change my
expression to still pull all occurrences of Tom Bonnette regardless of what
number is behind? I have tried using the wildcards, but it is not working.
Thanks!
 
R

Ragdyer

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("Tom Bonnette",'[Past Dues
Aug.xls]Sheet1'!$A$1:$A$5000))),'[Past Dues Aug.xls]Sheet1'!$B$1:$B$5000)
 
B

Bernard Liengme

Change
(--('[Past Dues Aug.xls]Sheet1'!$A$1:$A$5000="Tom Bonnette")
to
(--(LEFT('[Past Dues Aug.xls]Sheet1'!$A$1:$A$5000,12 )="Tom Bonnette")

or, if the name is in a cell (G1 for example)
(--(LEFT('[Past Dues Aug.xls]Sheet1'!$A$1:$A$5000,LEN(G1))=G1)

best wishes
 
Top