Sumproduct for partial text
 

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!

Sumproduct for partial text
 

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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jasmine" wrote in message
...
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!

Sumproduct for partial text
 

try:

=SUMPRODUCT(--(ISNUMBER(FIND("Tom Bonnette",$A$1:$A$5000))),$B$1:$B$5000)

"Jasmine" wrote:

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!

Sumproduct for partial text
 

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Jasmine" wrote in message
...
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!