Formula compatibiliity

[protocel3]

Hi, I am trying to get the following formulas to be compatible with previous versions of excel. currently the file is saved in XLSX but I would like it more universal.

=SUMPRODUCT((A:A>=DATEVALUE("12/1/2011"))*(A:A<=DATEVALUE("12/31/2011")),E:E)

I tried saving it as a xls but it lost a lot of formatting, I also completely redid the spreadsheet but it wouldn't accept the formula.


On a side note:
I am also trying to figure out why my linked cells (cell on one page = cell on a different page) got erased when I went from quickoffice to MS Office...but that's probably more application specific.

 

[wigi]

The point is, in XL2003, SUMPRODUCT cannot use whole column references, so you must guess at a range large enough to cover the largerst range you ever expect to need.

No. A dynamic range will work fine.

Like here: http://www.ozgrid.com/Excel/DynamicRanges.htm

 

[Rick Rothstein]

No. A dynamic range will work fine.

Like here: http://www.ozgrid.com/Excel/DynamicRanges.htm

Okay, yes, that's true, you can do that. I guess I did over-reach somewhat with my statement; however, by way of explanation, my personal preference is to avoid the Volatile Functions whenever possible.