Summing Text with Numbers
 

I need to sum a range of cells that will contain both text and numbers. The
text will always be an "s" and the number will be a number between .25 and
10. The entry in the cells to be counted will be entered as "s.25" or
"s.5.5", and the number following the "s." needs to be totaled.

Is there a way to accomplish this using the COUNTIF or SUM functions?

Summing Text with Numbers
 

Hi,

Assuming there are no blank cells in the range you can use

=SUMPRODUCT(MID(A1:A10,2,99)*1)

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"bgcooker" wrote:

I need to sum a range of cells that will contain both text and numbers. The
text will always be an "s" and the number will be a number between .25 and
10. The entry in the cells to be counted will be entered as "s.25" or
"s.5.5", and the number following the "s." needs to be totaled.

Is there a way to accomplish this using the COUNTIF or SUM functions?

Summing Text with Numbers
 

If "the number following 's.' needs to be totaled", use:
=SUMPRODUCT(MID(A1:A10,3,99)*1)

Regards,
Fred


"Mike H" wrote in message
...
Hi,

Assuming there are no blank cells in the range you can use

=SUMPRODUCT(MID(A1:A10,2,99)*1)

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"bgcooker" wrote:

I need to sum a range of cells that will contain both text and numbers.
The
text will always be an "s" and the number will be a number between .25
and
10. The entry in the cells to be counted will be entered as "s.25" or
"s.5.5", and the number following the "s." needs to be totaled.

Is there a way to accomplish this using the COUNTIF or SUM functions?

Summing Text with Numbers
 

Mike H -

Thanks for your help. This did not work for me. The formula returned the
#VALUE! comment.

I may not have stated clearly enough what is needed. The cells will have an
"s" entered as text, with a period and a numeric value as part of the string.
The numeric value will be a multiple of .25, up to a maximum of 10. For
example, some entries will be "s.25", "s.5", "s.5.75". Should they all be
shown with the same number of digits, for example: "s.0.25", "s.0.50",
"S.5.75", "s.0.1.00"?

Thank you



"Mike H" wrote:

Hi,

Assuming there are no blank cells in the range you can use

=SUMPRODUCT(MID(A1:A10,2,99)*1)

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"bgcooker" wrote:

I need to sum a range of cells that will contain both text and numbers. The
text will always be an "s" and the number will be a number between .25 and
10. The entry in the cells to be counted will be entered as "s.25" or
"s.5.5", and the number following the "s." needs to be totaled.

Is there a way to accomplish this using the COUNTIF or SUM functions?

Summing Text with Numbers
 

What if there are blanks in the range?

"bgcooker" wrote:

I need to sum a range of cells that will contain both text and numbers. The
text will always be an "s" and the number will be a number between .25 and
10. The entry in the cells to be counted will be entered as "s.25" or
"s.5.5", and the number following the "s." needs to be totaled.

Is there a way to accomplish this using the COUNTIF or SUM functions?

Summing Text with Numbers
 

Did you use my formula or Mike's? Mike's will give a #value error with
"s.5.75" whereas mine should work. However, both with fail with "s.0.1.00".
You would need to define what number you think this is.

Length of the number should be immaterial.

Regards,
Fred

"bgcooker" wrote in message
...
Mike H -

Thanks for your help. This did not work for me. The formula returned the
#VALUE! comment.

I may not have stated clearly enough what is needed. The cells will have
an
"s" entered as text, with a period and a numeric value as part of the
string.
The numeric value will be a multiple of .25, up to a maximum of 10. For
example, some entries will be "s.25", "s.5", "s.5.75". Should they all be
shown with the same number of digits, for example: "s.0.25", "s.0.50",
"S.5.75", "s.0.1.00"?

Thank you



"Mike H" wrote:

Hi,

Assuming there are no blank cells in the range you can use

=SUMPRODUCT(MID(A1:A10,2,99)*1)

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"bgcooker" wrote:

I need to sum a range of cells that will contain both text and numbers.
The
text will always be an "s" and the number will be a number between .25
and
10. The entry in the cells to be counted will be entered as "s.25" or
"s.5.5", and the number following the "s." needs to be totaled.

Is there a way to accomplish this using the COUNTIF or SUM functions?