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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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,
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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?