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

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

to prevent rounding errors.

Steve

3. 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. 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. 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. 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. 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
•