SUMIF and SUMIFS Error

[Jim]

It seems that I have to concatenate the criteria arguments in the
SUMIF and SUMIFS functions since I have moved to Excel 2007. For
example, ">D$4" must become ">"&D$4 in the Excel 2007 version. Is
this a change in syntax or a bug that needs to be addressed. Any
thoughts...

Jim

 

[Dave Peterson]

I think you're mis-remembering. That was the syntax required in xl2003 (as far
as =sumif() has been alive).

Well, unless you really wanted to use the string D$4 (not the cell with address
$D$4).

 

[Jim]

Even needing to concatenate the ">" sign and the cell reference? I am
not sure that I ever remember needing this syntax.

Here's another...conditional formatting has been "glitching" around a
lot, as well. Have you noticed anything weird with this? I have
tried the conditional formatting provided in the program and created
it on my own with the cell. For example:

=if("D4="Years",E4/F4,text(E4/F4,"0.0%"))

This formula works most of the time, but just stops working in some
instances until you edit the formula, altering nothing, and
recalculate. Same with the conditional formatting functionality built
into excel. (Note: I have already verified uniform formatting of the
source date.)

 

[Dave Peterson]

Yep to the concatenation question.

There are syntax errors in the formula you posted. So I'm hesitant to guess
what the problem could be.

But...

I've never seen this but there have been some posts where some formulas wouldn't
recalculate (with excel in automatic mode and the cells that the formula uses
changes).

One suggestion that seems to work at least temporarily is to:
Select all the cells
Edit|Replace
what: = (equal sign)
with: =
replace all

The change forces excel to recalculate each formula and seems to wake up its
calculation engine.

I'm not sure what could be going wrong with your conditional formatting, though.
You didn't post the rules you're using. But maybe the edit|replace technique
will fix it...