Solved: NUMBER STORED AS TEXT

[jammer6_9]

How can I SUM number stored as text :doh:

[Sir Babydum GBE]

Hi

Suppose you had a text-number in A1 and a real number in A2

Use: =SUM((A1:A2)*1) and enter using CTRL + Shift + Enter

BD

[jammer6_9]

Thanks for the reply. That works... By the why "*1"...


Hi

Suppose you had a text-number in A1 and a real number in A2

Use: =SUM((A1:A2)*1) and enter using CTRL + Shift + Enter

BD

[Sir Babydum GBE]

Thanks for the reply. That works...Because as long as Excel can recognise that text looks like a number, multiplying those cells by 1 forces it to treat any text-numbers as real numbers.

Using CTRL Shift Enter to input the formula forces the *1 calculation on all cells in the range.

[jammer6_9]

Got it... Thanks for the formula & expalnation... :friends:

[jammer6_9]

My apology, one more thing... there are sum rows hidden which I dont want to inlcude in the calculation.

I often use the formula in calculating real numbers as follows.
=SUBTOTAL(109,A1:A10) - which exclude hidden rows in calculation.

Now how will I sum or subtotal text-number excluding hidden rows...

[Sir Babydum GBE]

My apology, one more thing... there are sum rows hidden which I dont want to inlcude in the calculation.

I often use the formula in calculating real numbers as follows.
=SUBTOTAL(109,A1:A10) - which exclude hidden rows in calculation.

Now how will I sum or subtotal text-number excluding hidden rows...

I don't know how to do that with a formula. If you don't need the problem numbers to be formatted as text, you can convert them using VBA or, where Column A contains the source numbers, use column B (or another free column) and use, say, =A1*1 in the appropriate cells, then copy the formula range, paste as values, then paste over the original column.

I bit messy I know, but it works...

[Bob Phillips]

'--------------------------------------------------------------------------
Function IsVisible(ByVal rng As Range)
'--------------------------------------------------------------------------
Dim oRange As Range
Dim i As Long
Dim ary()

If rng.Rows.Count > 1 And _
rng.Columns.Count > 1 Then
IsVisible = CVErr(xlErrRef)
Else
If rng.Rows.Count > rng.Columns.Count Then
ReDim ary(1 To 1, 1 To rng.Rows.Count)
For Each oRange In rng.Rows
i = i + 1
ary(1, i) = Not oRange.EntireRow.Hidden
Next oRange
IsVisible = Application.Transpose(ary)
Else
ReDim ary(1 To 1, 1 To rng.Columns.Count)
For Each oRange In rng.Columns
i = i + 1
ary(1, i) = Not oRange.EntireColumn.Hidden
Next oRange
IsVisible = ary
End If
End If
End Function


and use like

=SUMPRODUCT(--(IsVisible(B2:B20)),--A2:A20)

where B is the filtered column, A the text amounts column.