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


--
Regards
Frank Kabel
Frankfurt, Germany


MollyDavis wrote:
> 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

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

Jerry

Frank Kabel wrote:

> 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
>
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> MollyDavis wrote:
>
>>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
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)


--
Regards
Frank Kabel
Frankfurt, Germany


Jerry W. Lewis wrote:
> You could write a VBA function that would check the format.
>
> Jerry
>
> Frank Kabel wrote:
>
>> 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
>>
>>
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>>
>>
>> MollyDavis wrote:
>>
>>> 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

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"MollyDavis" <molly_davis@pulitzer_prize.com> wrote in message
news:(E-Mail Removed)...
> 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


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.659 / Virus Database: 423 - Release Date: 15/04/2004