SUMIFS() error?

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?

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?

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?

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" <[email protected]> wrote in message
news:[email protected]...
> 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?

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:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
>> 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?
>
>

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:[email protected]...
>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:[email protected]...
>>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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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?
>>
>>
>
>