FIND text within text then sum
 

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

FIND text within text then sum
 

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

FIND text within text then sum
 

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

.

FIND text within text then sum
 

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

FIND text within text then sum
 

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
.