SumIf not working over a long range
 

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 100
=+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?

SumIf not working over a long range
 

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
wrote in message
...
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 100
=+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?

SumIf not working over a long range
 

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,

SumIf not working over a long range
 

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

"Adam Lawrence Acoustics" <Adam Lawrence
wrote in message
...
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 100
=+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?

SumIf not working over a long range
 

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

SumIf not working over a long range
 

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.