Cell format to use for calculation

[scott]

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.

 

[Gary''s Student]

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.

 

[Teethless mama]

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

 

[scott]

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]

It converts TRUE/FALSE to 1/0

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!