I have a range a1:a150 that can contain"rx" and could possibly contain
"rx(2)" which means there are 2 "rx" at location. Note: The same field can
have "rx,dx,sx" or "rx(2),dx,sx(4)", etc.

I want to count the occurrences when a1:a150 contains "rx" and add to count
by whatever is included in parentheses.

For example if a1-a5 contains "rx(2),sx" and a6-a10 contains "rx,dx" the
value of formula should return 15 because. I need to allow for multple
values per cell so I can do similar calcs based on different values
separated by commas.

Thanks in advance.

=sum(if(iserror(find("rx(",a1:A150)),if(iserror(fi nd("rx",A1:A150)),0,1),value(mid(A1:A150,find("rx( ",A1:A150)+3,1))))

enter as an array function (Control-Shift-Enter)

"Ed Gregory" wrote:

I have a range a1:a150 that can contain"rx" and could possibly contain
"rx(2)" which means there are 2 "rx" at location. Note: The same field can
have "rx,dx,sx" or "rx(2),dx,sx(4)", etc.

I want to count the occurrences when a1:a150 contains "rx" and add to count
by whatever is included in parentheses.

For example if a1-a5 contains "rx(2),sx" and a6-a10 contains "rx,dx" the
value of formula should return 15 because. I need to allow for multple
values per cell so I can do similar calcs based on different values
separated by commas.

Thanks in advance.

Thank you very much!!!


"bj" wrote in message
...

=sum(if(iserror(find("rx(",a1:A150)),if(iserror(fi nd("rx",A1:A150)),0,1),value(mid(A1:A150,find("rx( ",A1:A150)+3,1))))

enter as an array function (Control-Shift-Enter)

"Ed Gregory" wrote:

I have a range a1:a150 that can contain"rx" and could possibly contain
"rx(2)" which means there are 2 "rx" at location. Note: The same field
can
have "rx,dx,sx" or "rx(2),dx,sx(4)", etc.

I want to count the occurrences when a1:a150 contains "rx" and add to
count
by whatever is included in parentheses.

For example if a1-a5 contains "rx(2),sx" and a6-a10 contains "rx,dx" the
value of formula should return 15 because. I need to allow for multple
values per cell so I can do similar calcs based on different values
separated by commas.

Thanks in advance.

Can be shortened to...

=SUM(IF(ISNUMBER(SEARCH("rx(",A1:A10)),MID(A1:A10, SEARCH("rx(",A1:A10)+3,
1)+0))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"bj" wrote:

=sum(if(iserror(find("rx(",a1:A150)),if(iserror(fi nd("rx",A1:A150)),0,1),value
(mid(A1:A150,find("rx(",A1:A150)+3,1))))

enter as an array function (Control-Shift-Enter)