Conditional Sums and Excel 2007

[mghoran]

Are there differences between the way 2003 and 2007 works for Cond Sums? Previous .xls files work OK (2003), now getting "zero" values in 2007...

Cell formats, settings ?? I created these by "saving as" from the .xls, and the "rollup" had to be .xlsm because of macros.

I am linking multiple .xlsx files to one "rollup" .xlsm file that I need a sum based on a criteria, and sum them up when the other linked files are closed..

=SUM(IF('\\mw\data\f15pgms\me_10x_friet\ME_Action_Logs_2012\[ANR_ACTION_ITEMS.xlsx]Sheet1'!$F$2:$F$5000="bl",'\\mw\data\f15pgms\me_10x_friet\ME_Action_Logs_2012\[ANR_ACTION_ITEMS.xlsx]Sheet1'!$F$2:$F$5000,0))

 

[Firefly2012]

Hi

There's no difference in the way array formulas will work between the versions.

From your formula, you are conditionally checking that F equals the string "bl" then summing these same F cells up - this will always return a zero whether in 2003 or 2007 as you are summing Text values.

 

[mghoran]

That same formula works in Excel 2003.. sums up all the rows that have a "bl" in them..

 

[Firefly2012]

That same formula works in Excel 2003.. sums up all the rows that have a "bl" in them..

It is summing the cells that contain "bl" - it just happens that if F2:F5 each contain "bl" then

=SUM(F2:F5)

will always return zero (as the text values are ignored). This behaviour hasn't changed.

Did you perhaps mean you wanted to count the rows that contain "bl"? That would mean only a slight variation on your formula:

=SUM(IF('\\mw\data\f15pgms\me_10x_friet\ME_Action_Logs_2012\[ANR_ACTION_ITEMS.xlsx]Sheet1'!$F$2:$F$5000="bl",1,0))

which requires array-entering

 

[mghoran]

that worked perfectly.. THANK YOU and Happy New Year to you..

Not sure why the Conditional Sum Wizard did not make the formula work correctly though...