Can sumif detect text strings or detect if a dollar sign $ is in a cell?

[MollyDavis]

Hi,

I copied my online bank statement into excel, and it's all in one
column. I want excel to only sum those cells which contain a dollar sign
and ignore the banks comment and date cells in the sum.

I tried =SUMIF(A1:A500,"$") AND ALSO =SUMIF(A1:A500,"$",A1:A500)

I just get 0 for the result.

Anyone know how to get sumif to match on a character like the dollar
sign? Seems like this would be an obvious and functional use for this
function eh?

Thanks!

Love,

Me

 

[Frank Kabel]

Hi
the dollar sign is probably created by a format? If this is true you
can't check for this sign. You'll need VBA to check the format of the
cell and sum the values within a loop

If the value in your cells is a string with a $ sign manually entered
you have to strip the number from this letter before you can sum them.

So please provide some more detail about the nature of your values

 

[Jerry W. Lewis]

You could write a VBA function that would check the format.

Jerry

 

[Frank Kabel]

Hi
you may use the following user defined function to count FORMATED
dollar values. Put the following in one of your standard modules:
Public Function sum_dollar(rng As Range)
Dim cell As Range
Dim ret_value
Dim format_info

For Each cell In rng
If IsNumeric(cell.Value) Then
format_info = cell.NumberFormat
If InStr(format_info, "$") > 0 Then
ret_value = ret_value + cell.Value
End If
End If
Next
sum_dollar = ret_value
End Function


Now you can use the following formula in your worksheet
=SUM_DOLLAR(A1:A500)

 

[Ken Wright]

Unless you have individual entries in your statement that exceed circa $38,000,
then you could probably get away with just summing everything less than that or
say 30,000, as that is the value of the current date.