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

  • Thread starter Thread starter MollyDavis
  • Start date Start date
M

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
 
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
 
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)
 
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.
 
Back
Top