Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Tokyo, Japan
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Sum (Excel 2002)

    I have attached a spreadsheet with two columns the currency symbol column and the amount column. I have accomplished summing the amounts at the bottom using conditional sum. Although this is fine I would like to know if there is another way to accomplish the individual currency total without the first column. Much like a conditional sum based on the currency format of the amount column. The amount column will grow as more data are eventually inputted. Thank you in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Sum (Excel 2002)

    You'd need to write a custom function that parses the NumberFormat property to look for a currency symbol. Not very attractive.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Sum (Excel 2002)

    How about this function in a module:

    <pre>Option Explicit
    Function SumText(rng As Range, str As String)
    Dim rCell As Range
    Dim dSum As Double
    dSum = 0
    For Each rCell In rng
    If InStr(rCell.Text, str) <> 0 Then _
    dSum = dSum + rCell.Value
    Next
    SumText = dSum
    End Function</pre>


    And use as formulas:
    <pre>=SumText($B$2:$B$21,"

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Tokyo, Japan
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Sum (Excel 2002)

    Steve you are a wonder. Thanks again. Hans appreciate the suggestion as well. This lounge really rocks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •