Hi,

Column A contains text and numbers with some recurring text ie
1234 xxx 098
1342 xxx 878
5642 xxx 456

Column B contains a number.

I need to sum column b on selected text criteria in Column A. I can't use
LEN or RIGHT as the number of characters in column A are never the same and
there isn't always a space between numbers and text. I thought trying to use
a SUMPRODUCT with a FIND as one of the array options, but for the life of me
can't make it work.

Help please

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dhazmo" wrote in message
...
Hi,

Column A contains text and numbers with some recurring text ie
1234 xxx 098
1342 xxx 878
5642 xxx 456

Column B contains a number.

I need to sum column b on selected text criteria in Column A. I can't use
LEN or RIGHT as the number of characters in column A are never the same
and
there isn't always a space between numbers and text. I thought trying to
use
a SUMPRODUCT with a FIND as one of the array options, but for the life of
me
can't make it work.

Help please

Don - sorry can't send you the data - is there any other information I can
supply you with?

"Don Guillett" wrote:

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dhazmo" wrote in message
...
Hi,

Column A contains text and numbers with some recurring text ie
1234 xxx 098
1342 xxx 878
5642 xxx 456

Column B contains a number.

I need to sum column b on selected text criteria in Column A. I can't use
LEN or RIGHT as the number of characters in column A are never the same
and
there isn't always a space between numbers and text. I thought trying to
use
a SUMPRODUCT with a FIND as one of the array options, but for the life of
me
can't make it work.

Help please

.

Maybe you can use =sumif() since it accepts wild cards.

=sumif(a:a,"*xxx*",b:b)
or if xxx was in z99
=sumif(a:a,"*"&z99&"*",b:b)

I'd check for empty cells first:
=if(z99="","",sumif(...))

Dhazmo wrote:

Hi,

Column A contains text and numbers with some recurring text ie
1234 xxx 098
1342 xxx 878
5642 xxx 456

Column B contains a number.

I need to sum column b on selected text criteria in Column A. I can't use
LEN or RIGHT as the number of characters in column A are never the same and
there isn't always a space between numbers and text. I thought trying to use
a SUMPRODUCT with a FIND as one of the array options, but for the life of me
can't make it work.

Help please

--

Dave Peterson

Thank you!!! - Didn't even think of using wild cards.

"Dave Peterson" wrote:

Maybe you can use =sumif() since it accepts wild cards.

=sumif(a:a,"*xxx*",b:b)
or if xxx was in z99
=sumif(a:a,"*"&z99&"*",b:b)

I'd check for empty cells first:
=if(z99="","",sumif(...))

Dhazmo wrote:

Hi,

Column A contains text and numbers with some recurring text ie
1234 xxx 098
1342 xxx 878
5642 xxx 456

Column B contains a number.

I need to sum column b on selected text criteria in Column A. I can't use
LEN or RIGHT as the number of characters in column A are never the same and
there isn't always a space between numbers and text. I thought trying to use
a SUMPRODUCT with a FIND as one of the array options, but for the life of me
can't make it work.

Help please

--

Dave Peterson
.