SumIf not working over a long range

[Adam Lawrence Acoustics]

Hi,

I've used SumIf quite a lot to summaraise data in a spreadsheet, which has
always worked fine, however I'm now trying to sum ranges over some 16,500
data points, and now I have this much larger range the calculations dont seem
to work, whereas they always have over much shorter ranges.

I'm just testing with a couple of numbers to see that it works and these
tests are not working.

Two examples:

I expect this to return 10
=+SUMIF(GridCalcs!Z$3:Z$17001,">=0.1",GridCalcs!$D$3:$D$17001)-SUM(AG13:AG$15)
because cell Z16544 = 0.1 and Cell D16544 = 10.

I expect this to return 10
=+SUMIF(GridCalcs!BC$3:BC$17001,"<=-0.1",GridCalcs!$E$3:$E$17001)-SUM(O19:O$21)
because cell BC16545 = -0.1 and Cell D16545 = 100

Both sums actually return zero. All the other values in column D are set to
0 for the purposes of the test. The problem only seems to occur with the 0.1
and -0.1 values, and of course if I set up a simple test in another workbook,
that works fine.

Oh, this is Excel 2003, SP2. The spreadsheet is trying to do quite a lot of
results processing for me(!). The file size is a shade under 200MB.

Any thoughts?

 

[Peo Sjoblom]

You can remove the obsolete plus sign after the equal sign.
It has no functionality whatsoever.

My guess is that perhaps you have the cells formatted as
number with 1 decimal but the values are in fact less than 0.1

meaning the display will be rounded but the real value might still be
less


--


Regards,


Peo Sjoblom

"Adam Lawrence Acoustics" <Adam Lawrence
(e-mail address removed)> wrote in message
news:[email protected]...

 

[Adam Lawrence Acoustics]

Thanks,

I know about the + and know its redundancy, its just an easy keyboard way of
automatically getting into formula mode. I accept there's probably another
easy way of doing this.

The cells in the columns are pulled from another part of the spreadsheet
which rounds to one decimal place, so I know they are all 0.1's and -0.1's,

 

[Don Guillett]

I don't think the size of the range has anything to do with it. I could not
replicate your problem. Probably formatting.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
"Adam Lawrence Acoustics" <Adam Lawrence
(e-mail address removed)> wrote in message
news:[email protected]...

 

[Adam Lawrence Acoustics]

Thanks, I tried a Copy-PasteValues-RemoveFormatting to check, and indeed it
turns out that a simple subtraction 75.2(exactly) - 75.3(exactly) =
-0.0999999999999943(!)

So, that answers one question, but poses another one!

Adam

 

[Adam Lawrence Acoustics]

Answered my own other question by looking up answers to rounding problems.
For those that are interested, the topic "Doesn't add up correctly" gave good
info.