# Cell format to use for calculation

S

#### 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.

G

#### 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

T

#### Teethless mama

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

S

#### 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!

T

#### Teethless mama

It converts TRUE/FALSE to 1/0

scott said:
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!