Cell format to use for calculation
 

I have multiple spreadsheets in a workbook and each worksheet contains
recorded rainfall data for each day of a month. In the column containing the
recorded rainfall, the data is entered as:

1

or

1"

I need to add the totals for each month, but I cannot since some of the
contains contain alphanumeric values.is there a function that I can use that
can disregard the text and focus only on the numeric value of the cell
instead of having to copy the value into a new cell each time I want to add?

Thanks.

Cell format to use for calculation
 

Try this small UDF:

Public Function zum(r As Range) As Variant
zum = 0
For Each rr In r
zum = zum + Val(rr.Value)
Next
End Function


You can use it in the worksheet like SUM(). It will ignore trailing text in
cells that start with a number, but end with text.
--
Gary''s Student - gsnu200802


"scott" wrote:

I have multiple spreadsheets in a workbook and each worksheet contains
recorded rainfall data for each day of a month. In the column containing the
recorded rainfall, the data is entered as:

1

or

1"

I need to add the totals for each month, but I cannot since some of the
contains contain alphanumeric values.is there a function that I can use that
can disregard the text and focus only on the numeric value of the cell
instead of having to copy the value into a new cell each time I want to add?

Thanks.

Cell format to use for calculation
 

=SUMPRODUCT(--SUBSTITUTE(A1:A4,"""",""))


"scott" wrote:

I have multiple spreadsheets in a workbook and each worksheet contains
recorded rainfall data for each day of a month. In the column containing the
recorded rainfall, the data is entered as:

1

or

1"

I need to add the totals for each month, but I cannot since some of the
contains contain alphanumeric values.is there a function that I can use that
can disregard the text and focus only on the numeric value of the cell
instead of having to copy the value into a new cell each time I want to add?

Thanks.

Cell format to use for calculation
 

both suggestions worked wonderfully and thanks for your help. can you explain
the "--" in functions? To me, it logically seems that is a double-negative,
but I am sure there is a different meaning.
Thanks!

"Teethless mama" wrote:

=SUMPRODUCT(--SUBSTITUTE(A1:A4,"""",""))


"scott" wrote:

I have multiple spreadsheets in a workbook and each worksheet contains
recorded rainfall data for each day of a month. In the column containing the
recorded rainfall, the data is entered as:

1

or

1"

I need to add the totals for each month, but I cannot since some of the
contains contain alphanumeric values.is there a function that I can use that
can disregard the text and focus only on the numeric value of the cell
instead of having to copy the value into a new cell each time I want to add?

Thanks.

Cell format to use for calculation
 

It converts TRUE/FALSE to 1/0

"scott" wrote:

both suggestions worked wonderfully and thanks for your help. can you explain
the "--" in functions? To me, it logically seems that is a double-negative,
but I am sure there is a different meaning.
Thanks!

"Teethless mama" wrote:

=SUMPRODUCT(--SUBSTITUTE(A1:A4,"""",""))


"scott" wrote:

I have multiple spreadsheets in a workbook and each worksheet contains
recorded rainfall data for each day of a month. In the column containing the
recorded rainfall, the data is entered as:

1

or

1"

I need to add the totals for each month, but I cannot since some of the
contains contain alphanumeric values.is there a function that I can use that
can disregard the text and focus only on the numeric value of the cell
instead of having to copy the value into a new cell each time I want to add?

Thanks.