Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    sumif total (Excel 2002)

    I have a sumif formula that adds the following amounts:
    -1,400.25
    1,000.00
    400.25
    When it totals, the amount that is returned is (0.00)
    My macro won't work because it doesn't see it as a zero (showing up as a dash symbol like this "-")
    If I move the negative number to be AFTER the postive numbers in the database, then the result is the dash symbol and NOT the (0.00), then of course my macro works.
    Why is this occurring... I tried different number formats by copying formats etc... I'm not sure why I knew to try and move the negative number to be after the positive numbers in the first place, but it worked. My problem is that the database will be quite large and others who will use the tool won't know why there is a problem. Any ideas??
    Thanks!
    Lana

  2. #2
    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: sumif total (Excel 2002)

    Try rounding the sumif formula:
    =round(sumif(...),5)

    to prevent rounding errors.

    Steve

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

    Re: sumif total (Excel 2002)

    This is due to rounding errors. Excel internally stores numbers in binary format (consisting of 0 and 1). Most decimal numbers cannot be represented exactly in binary format, so the numbers are necessarily rounded off. When Excel adds these numbers, the rounding errors may reinforce each other or cancel out, and the order in which the numbers are processed may make a difference in this respect.
    You could use a formula like this:

    =ROUND(SUMIF(...),2)

    This will round the result of the SUMIF formula to 2 decimal places, so very small negative (or positive, for that matter) values will be rounded to 0.

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: sumif total (Excel 2002)

    This works when I do it in Excel... now I can't get it to work in my macro... here it is... thanks!
    Lana
    ElseIf Application.WorksheetFunction. _
    Round(SumIf(Sheets("Data").Range("I3:I15000"), _
    Sheets("Data").Range("I" & lngRow), _
    Sheets("Data").Range("G3:G15000")), 2) = 0 Then
    Sheets("Data").Range("J" & lngRow) = "Delete"

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

    Re: sumif total (Excel 2002)

    The order is the wrong way round:

    ElseIf Round(Application.WorksheetFunction. _
    SumIf(Sheets("Data").Range("I3:I15000"), _
    Sheets("Data").Range("I" & lngRow), _
    Sheets("Data").Range("G3:G15000")), 2) = 0 Then
    Sheets("Data").Range("J" & lngRow) = "Delete"

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: sumif total (Excel 2002)

    Oh darn... I thought about putting it there and didn't try it... I hate when that happens... thanks for helping me out Hans! And thanks to Steve as well!
    Lana

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

    Re: sumif total (Excel 2002)

    You could also have put Application.WorksheetFunction. before Round, but it's not necessary because Round is also a VBA function.
    You *must* have Application.WorksheetFunction. before SumIf because SumIf is not a VBA function, it's only an Excel worksheet function.

Posting Permissions

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