SUMIFS() error?

fgrose · External Since: Jul 05, 2006 Posts: 8

I'm trying the new SUMIFS() function in Excel 2007 beta2 and find that this
expression produces a #VALUE! error eventhough the function wizard evaluates
everything correctly and the step-by-step evaluation is fine until the said
final error.

Is this a bug?

fgrose · External Since: Jul 05, 2006 Posts: 8

Sorry, here is the expression: =SUMIFS(C3:C45,B3:B45,">"&B2,B3:B45,"<="&B3)

"fgrose" wrote:

> I'm trying the new SUMIFS() function in Excel 2007 beta2 and find that this
> expression produces a #VALUE! error eventhough the function wizard evaluates
> everything correctly and the step-by-step evaluation is fine until the said
> final error.
>
> Is this a bug?

fgrose · External Since: Jul 05, 2006 Posts: 8

The criteria ranges are dates and the the sum_range is are currency values.

I just noticed that the final formula result in the formula wizard give back
the input string, SUMIFS(C4:C45,B4:B45,">"&B2,B4:B45,"<="&B3), with the B of
B2 underscored in the first instance (under the intermediate results, which
are correct) and the B of B3 underscored in the second instance at the bottom
after the 'formula result =' label.

I've changed the range starts to B4 and C4 so they don't overlap with the
criteria cells, and I still suffer the #VALUE! error.

"fgrose" wrote:

> I'm trying the new SUMIFS() function in Excel 2007 beta2 and find that this
> expression produces a #VALUE! error eventhough the function wizard evaluates
> everything correctly and the step-by-step evaluation is fine until the said
> final error.
>
> Is this a bug?

Peo Sjoblom · External Since: Mar 21, 2006 Posts: 779

I believe it has something to do with the used range (it's a bug) if you
fill all the cells B4:C45 with numbers (any numbers) it will work, then if
you clear those numbers it will still work

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

"fgrose" <fgrose.TakeThisOut@discussions.microsoft.com> wrote in message
news:54710EE8-5202-4E62-9CC3-62FD7BA563B1@microsoft.com...
> The criteria ranges are dates and the the sum_range is are currency
> values.
>
> I just noticed that the final formula result in the formula wizard give
> back
> the input string, SUMIFS(C4:C45,B4:B45,">"&B2,B4:B45,"<="&B3), with the B
> of
> B2 underscored in the first instance (under the intermediate results,
> which
> are correct) and the B of B3 underscored in the second instance at the
> bottom
> after the 'formula result =' label.
>
> I've changed the range starts to B4 and C4 so they don't overlap with the
> criteria cells, and I still suffer the #VALUE! error.
>
> "fgrose" wrote:
>
>> I'm trying the new SUMIFS() function in Excel 2007 beta2 and find that
>> this
>> expression produces a #VALUE! error eventhough the function wizard
>> evaluates
>> everything correctly and the step-by-step evaluation is fine until the
>> said
>> final error.
>>
>> Is this a bug?

Peo Sjoblom · External Since: Mar 21, 2006 Posts: 779

I tested some more and if there are some empty cells in the criteria range
there will be a value error, empty meaning that the cells have always been
empty, however as soon as they have been filled it will work and if you fill
them with something and later delete it, it will still work so definitely a
bug. I filed a bug report on MS beta site

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

"Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
news:OmUvkP6hGHA.4504@TK2MSFTNGP03.phx.gbl...
>I believe it has something to do with the used range (it's a bug) if you
>fill all the cells B4:C45 with numbers (any numbers) it will work, then if
>you clear those numbers it will still work
>
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Excel 95 - Excel 2007
> Northwest Excel Solutions
> www.nwexcelsolutions.com
> "It is a good thing to follow the first law of holes;
> if you are in one stop digging." Lord Healey
>
>
> "fgrose" <fgrose.TakeThisOut@discussions.microsoft.com> wrote in message
> news:54710EE8-5202-4E62-9CC3-62FD7BA563B1@microsoft.com...
>> The criteria ranges are dates and the the sum_range is are currency
>> values.
>>
>> I just noticed that the final formula result in the formula wizard give
>> back
>> the input string, SUMIFS(C4:C45,B4:B45,">"&B2,B4:B45,"<="&B3), with the B
>> of
>> B2 underscored in the first instance (under the intermediate results,
>> which
>> are correct) and the B of B3 underscored in the second instance at the
>> bottom
>> after the 'formula result =' label.
>>
>> I've changed the range starts to B4 and C4 so they don't overlap with the
>> criteria cells, and I still suffer the #VALUE! error.
>>
>> "fgrose" wrote:
>>
>>> I'm trying the new SUMIFS() function in Excel 2007 beta2 and find that
>>> this
>>> expression produces a #VALUE! error eventhough the function wizard
>>> evaluates
>>> everything correctly and the step-by-step evaluation is fine until the
>>> said
>>> final error.
>>>
>>> Is this a bug?
>
>

Peo Sjoblom · External Since: Mar 21, 2006 Posts: 779

I received an answer saying that this bug has been fixed in the next build

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

"Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
news:eLSHCREiGHA.4144@TK2MSFTNGP02.phx.gbl...
>I tested some more and if there are some empty cells in the criteria range
>there will be a value error, empty meaning that the cells have always been
>empty, however as soon as they have been filled it will work and if you
>fill them with something and later delete it, it will still work so
>definitely a bug. I filed a bug report on MS beta site
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Excel 95 - Excel 2007
> Northwest Excel Solutions
> www.nwexcelsolutions.com
> "It is a good thing to follow the first law of holes;
> if you are in one stop digging." Lord Healey
>
>
> "Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> news:OmUvkP6hGHA.4504@TK2MSFTNGP03.phx.gbl...
>>I believe it has something to do with the used range (it's a bug) if you
>>fill all the cells B4:C45 with numbers (any numbers) it will work, then if
>>you clear those numbers it will still work
>>
>>
>>
>> --
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>> Excel 95 - Excel 2007
>> Northwest Excel Solutions
>> www.nwexcelsolutions.com
>> "It is a good thing to follow the first law of holes;
>> if you are in one stop digging." Lord Healey
>>
>>
>> "fgrose" <fgrose.TakeThisOut@discussions.microsoft.com> wrote in message
>> news:54710EE8-5202-4E62-9CC3-62FD7BA563B1@microsoft.com...
>>> The criteria ranges are dates and the the sum_range is are currency
>>> values.
>>>
>>> I just noticed that the final formula result in the formula wizard give
>>> back
>>> the input string, SUMIFS(C4:C45,B4:B45,">"&B2,B4:B45,"<="&B3), with the
>>> B of
>>> B2 underscored in the first instance (under the intermediate results,
>>> which
>>> are correct) and the B of B3 underscored in the second instance at the
>>> bottom
>>> after the 'formula result =' label.
>>>
>>> I've changed the range starts to B4 and C4 so they don't overlap with
>>> the
>>> criteria cells, and I still suffer the #VALUE! error.
>>>
>>> "fgrose" wrote:
>>>
>>>> I'm trying the new SUMIFS() function in Excel 2007 beta2 and find that
>>>> this
>>>> expression produces a #VALUE! error eventhough the function wizard
>>>> evaluates
>>>> everything correctly and the step-by-step evaluation is fine until the
>>>> said
>>>> final error.
>>>>
>>>> Is this a bug?
>>
>>
>
>

txsharla · External Since: Oct 29, 2009 Posts: 1

Peo,
I was having the same problem as the original poster but now it's 10/2009
and I'm using the most current version of Excel 2007. I've solved the
immediate problem with a sumproduct calc but I'm really frustrated that I
can't use sumifs if I have blank rows amond my data. Do you know why it
hasn't been fixed?

"Peo Sjoblom" wrote:

> I received an answer saying that this bug has been fixed in the next build
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Excel 95 - Excel 2007
> Northwest Excel Solutions
> www.nwexcelsolutions.com
> "It is a good thing to follow the first law of holes;
> if you are in one stop digging." Lord Healey
>
>
> "Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> news:eLSHCREiGHA.4144@TK2MSFTNGP02.phx.gbl...
> >I tested some more and if there are some empty cells in the criteria range
> >there will be a value error, empty meaning that the cells have always been
> >empty, however as soon as they have been filled it will work and if you
> >fill them with something and later delete it, it will still work so
> >definitely a bug. I filed a bug report on MS beta site
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > Excel 95 - Excel 2007
> > Northwest Excel Solutions
> > www.nwexcelsolutions.com
> > "It is a good thing to follow the first law of holes;
> > if you are in one stop digging." Lord Healey
> >
> >
> > "Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> > news:OmUvkP6hGHA.4504@TK2MSFTNGP03.phx.gbl...
> >>I believe it has something to do with the used range (it's a bug) if you
> >>fill all the cells B4:C45 with numbers (any numbers) it will work, then if
> >>you clear those numbers it will still work
> >>
> >>
> >>
> >> --
> >>
> >> Regards,
> >>
> >> Peo Sjoblom
> >>
> >> Excel 95 - Excel 2007
> >> Northwest Excel Solutions
> >> www.nwexcelsolutions.com
> >> "It is a good thing to follow the first law of holes;
> >> if you are in one stop digging." Lord Healey
> >>
> >>
> >> "fgrose" <fgrose.DeleteThis@discussions.microsoft.com> wrote in message
> >> news:54710EE8-5202-4E62-9CC3-62FD7BA563B1@microsoft.com...
> >>> The criteria ranges are dates and the the sum_range is are currency
> >>> values.
> >>>
> >>> I just noticed that the final formula result in the formula wizard give
> >>> back
> >>> the input string, SUMIFS(C4:C45,B4:B45,">"&B2,B4:B45,"<="&B3), with the
> >>> B of
> >>> B2 underscored in the first instance (under the intermediate results,
> >>> which
> >>> are correct) and the B of B3 underscored in the second instance at the
> >>> bottom
> >>> after the 'formula result =' label.
> >>>
> >>> I've changed the range starts to B4 and C4 so they don't overlap with
> >>> the
> >>> criteria cells, and I still suffer the #VALUE! error.
> >>>
> >>> "fgrose" wrote:
> >>>
> >>>> I'm trying the new SUMIFS() function in Excel 2007 beta2 and find that
> >>>> this
> >>>> expression produces a #VALUE! error eventhough the function wizard
> >>>> evaluates
> >>>> everything correctly and the step-by-step evaluation is fine until the
> >>>> said
> >>>> final error.
> >>>>
> >>>> Is this a bug?
> >>
> >>
> >
> >
>
>
>