Excel column sumif is incorrect

This is a sheet we use for inventory purposes. The sheet calculates correctly
in Excel 2000.

There are two columns of data, one containing a number of cases, the other
the type of case used.

The sumif checks if the case type is equal to "8850", if it is, it sums the
relevant row in the number of cases column. The exact formula is:
"=SUMIF($X$3:$X$629,"8850",$O$3:$O$629)".

If I copy the data out of the columns into a new sheet and perform the same
operation it works correctly. However, in the sheet the data is in the sum
comes out incorrect.

Hi
Just a thought. Maybe some of your "8850" cells are actually numbers which
would be ignored by the formula.

Andy.

"Joseph Peralta" <JosephPeralta@discussions.microsoft.com> wrote in message
news:4F6248B5-0262-4737-B1B4-E223743F9FEC@microsoft.com...
> This is a sheet we use for inventory purposes. The sheet calculates
> correctly
> in Excel 2000.
>
> There are two columns of data, one containing a number of cases, the other
> the type of case used.
>
> The sumif checks if the case type is equal to "8850", if it is, it sums
> the
> relevant row in the number of cases column. The exact formula is:
> "=SUMIF($X$3:$X$629,"8850",$O$3:$O$629)".
>
> If I copy the data out of the columns into a new sheet and perform the
> same
> operation it works correctly. However, in the sheet the data is in the sum
> comes out incorrect.

Thanks for the response, Andy.

Sadly, that's not the case, that was one of the first things I thought of
too. I went through and set all the type cells to text and number cells to
number.

If it matters this is Excel 2007 Beta, I got here from the beta page and
didn't notice this was a general Excel newsgroup.

"Andy" wrote:

> Hi
> Just a thought. Maybe some of your "8850" cells are actually numbers which
> would be ignored by the formula.
>
> Andy.
>
> "Joseph Peralta" <JosephPeralta@discussions.microsoft.com> wrote in message
> news:4F6248B5-0262-4737-B1B4-E223743F9FEC@microsoft.com...
> > This is a sheet we use for inventory purposes. The sheet calculates
> > correctly
> > in Excel 2000.
> >
> > There are two columns of data, one containing a number of cases, the other
> > the type of case used.
> >
> > The sumif checks if the case type is equal to "8850", if it is, it sums
> > the
> > relevant row in the number of cases column. The exact formula is:
> > "=SUMIF($X$3:$X$629,"8850",$O$3:$O$629)".
> >
> > If I copy the data out of the columns into a new sheet and perform the
> > same
> > operation it works correctly. However, in the sheet the data is in the sum
> > comes out incorrect.
>
>
>

Hi
You could try this:
=SUMPRODUCT(--($X$3:$X$629="8850"),--(,$O$3:$O$629))
and see if you get a similar result.

Andy.

"Joseph Peralta" <JosephPeralta@discussions.microsoft.com> wrote in message
news:D107A4F2-51DB-4C60-BE84-2B4D41A212D5@microsoft.com...
> Thanks for the response, Andy.
>
> Sadly, that's not the case, that was one of the first things I thought of
> too. I went through and set all the type cells to text and number cells to
> number.
>
> If it matters this is Excel 2007 Beta, I got here from the beta page and
> didn't notice this was a general Excel newsgroup.
>
> "Andy" wrote:
>
>> Hi
>> Just a thought. Maybe some of your "8850" cells are actually numbers
>> which
>> would be ignored by the formula.
>>
>> Andy.
>>
>> "Joseph Peralta" <JosephPeralta@discussions.microsoft.com> wrote in
>> message
>> news:4F6248B5-0262-4737-B1B4-E223743F9FEC@microsoft.com...
>> > This is a sheet we use for inventory purposes. The sheet calculates
>> > correctly
>> > in Excel 2000.
>> >
>> > There are two columns of data, one containing a number of cases, the
>> > other
>> > the type of case used.
>> >
>> > The sumif checks if the case type is equal to "8850", if it is, it sums
>> > the
>> > relevant row in the number of cases column. The exact formula is:
> > "=SUMIF($X$3:$X$629,"8850",$O$3:$O$629)".
>> >
>> > If I copy the data out of the columns into a new sheet and perform the
>> > same
>> > operation it works correctly. However, in the sheet the data is in the
>> > sum
>> > comes out incorrect.
>>
>>
>>

Thanks for the help. This formula allows the cell to evaluate correctly. I'm
going to continue looking at this to see if I can find the actual root of the
problem however, as the other formula works perfectly well in every other
cell on that sheet that we use it in.

Joe

"Andy" wrote:

> Hi
> You could try this:
> =SUMPRODUCT(--($X$3:$X$629="8850"),--(,$O$3:$O$629))
> and see if you get a similar result.
>
> Andy.
>
> "Joseph Peralta" <JosephPeralta@discussions.microsoft.com> wrote in message
> news:D107A4F2-51DB-4C60-BE84-2B4D41A212D5@microsoft.com...
> > Thanks for the response, Andy.
> >
> > Sadly, that's not the case, that was one of the first things I thought of
> > too. I went through and set all the type cells to text and number cells to
> > number.
> >
> > If it matters this is Excel 2007 Beta, I got here from the beta page and
> > didn't notice this was a general Excel newsgroup.
> >
> > "Andy" wrote:
> >
> >> Hi
> >> Just a thought. Maybe some of your "8850" cells are actually numbers
> >> which
> >> would be ignored by the formula.
> >>
> >> Andy.
> >>
> >> "Joseph Peralta" <JosephPeralta@discussions.microsoft.com> wrote in
> >> message
> >> news:4F6248B5-0262-4737-B1B4-E223743F9FEC@microsoft.com...
> >> > This is a sheet we use for inventory purposes. The sheet calculates
> >> > correctly
> >> > in Excel 2000.
> >> >
> >> > There are two columns of data, one containing a number of cases, the
> >> > other
> >> > the type of case used.
> >> >
> >> > The sumif checks if the case type is equal to "8850", if it is, it sums
> >> > the
> >> > relevant row in the number of cases column. The exact formula is:
> > > "=SUMIF($X$3:$X$629,"8850",$O$3:$O$629)".
> >> >
> >> > If I copy the data out of the columns into a new sheet and perform the
> >> > same
> >> > operation it works correctly. However, in the sheet the data is in the
> >> > sum
> >> > comes out incorrect.
> >>
> >>
> >>
>
>
>

Changing the format of the cell won't change the underlying value.

Try changing the format of the cell to General and then reenter that value
(F2|Enter would be enough).

If you have lots of these "numbers" to convert, you can select an empty cell.

Edit|copy
select the offending range
edit|Paste special|check Add.



Joseph Peralta wrote:
>
> Thanks for the response, Andy.
>
> Sadly, that's not the case, that was one of the first things I thought of
> too. I went through and set all the type cells to text and number cells to
> number.
>
> If it matters this is Excel 2007 Beta, I got here from the beta page and
> didn't notice this was a general Excel newsgroup.
>
> "Andy" wrote:
>
> > Hi
> > Just a thought. Maybe some of your "8850" cells are actually numbers which
> > would be ignored by the formula.
> >
> > Andy.
> >
> > "Joseph Peralta" <JosephPeralta@discussions.microsoft.com> wrote in message
> > news:4F6248B5-0262-4737-B1B4-E223743F9FEC@microsoft.com...
> > > This is a sheet we use for inventory purposes. The sheet calculates
> > > correctly
> > > in Excel 2000.
> > >
> > > There are two columns of data, one containing a number of cases, the other
> > > the type of case used.
> > >
> > > The sumif checks if the case type is equal to "8850", if it is, it sums
> > > the
> > > relevant row in the number of cases column. The exact formula is:
> > > "=SUMIF($X$3:$X$629,"8850",$O$3:$O$629)".
> > >
> > > If I copy the data out of the columns into a new sheet and perform the
> > > same
> > > operation it works correctly. However, in the sheet the data is in the sum
> > > comes out incorrect.
> >
> >
> >

--

Dave Peterson