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]

Try to use a certain range for your SUMPRODUCT formula, instead of entire columns. The other parts of the formula should be fine IMO.

 

[protocel3]

Is there a simpler formula to this:

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

I have found this to be one of my biggest issues, I specifically need it to do the exact same thing as the string above but must be able to do it in MS Office, OpenOffice, and QuickOffice.

I have tried various alternative of the string but the results don't come out right.

 

[wigi]

I do not know about OO and QuickOffice, only MS Office.

Given that you have Excel 2007 or 2010, SUMIFS is an alternative. Give this a whirl please, in the 3 said programs.

 

[T. Valko]

Is there a simpler formula to this:

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

I have found this to be one of my biggest issues, I specifically need it to do the exact same thing as the string above but must be able to do it in MS Office, OpenOffice, and QuickOffice.

I have tried various alternative of the string but the results don't come out right.

If you want it to be compatible with earlier versions of Excel then you can't use entire columns as range references with the SUMPRODUCT function.

=SUMPRODUCT(--(TEXT(A2:A100,"mmmyyyy")="Dec2011"),E2:E100)

 

[protocel3]

Tried SUMIFS, but only seem to get a result of "0" to show up.

 

[protocel3]

OK, Found a solution:

=SUM(IF(A:A>=DATE(2011,1,1),IF(A:A<=DATE(2011,1,31),D:D),0))

and

CTRL+SHIFT+ENTER

and it works like a charm in all 3 programs.

Trying to keep this spreadsheet editable and compatible with all 3 of those programs has been a complete nightmare, but this will do until full compatibility is established in the world....but good luck getting that.

 

[protocel3]

If you want it to be compatible with earlier versions of Excel then you can't use entire columns as range references with the SUMPRODUCT function.

=SUMPRODUCT(--(TEXT(A2:A100,"mmmyyyy")="Dec2011"),E2:E100)

The problem with that is if the data goes beyond the range which is very very likely since this spreadsheet is to record data for the entire year (various purchases, which could be in the thousands depending on how good or bad the year is), so I have to use the entire column. Of course unless I use individual files per month, but I like to compare the months as I go.

Now I just need to figure out how to get a set of cells (3x13) to "float" as I scroll so I can see my totals no matter where I am scrolled to.

 

[Rick Rothstein]

The problem with that is if the data goes beyond the range which is very very likely since this spreadsheet is to record data for the entire year (various purchases, which could be in the thousands depending on how good or bad the year is), so I have to use the entire column.

You only need to use a range larger than the largest amount of data you ever expect. You said "thousands"... would 10,000 rows cover it?

=SUMPRODUCT(--(TEXT(A2:A10000,"mmmyyyy")="Dec2011"),E2:E10000)

If not, then what about 20000 rows?

=SUMPRODUCT(--(TEXT(A2:A20000,"mmmyyyy")="Dec2011"),E2:E20000)

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.

 

[protocel3]

You only need to use a range larger than the largest amount of data you ever expect. You said "thousands"... would 10,000 rows cover it?

=SUMPRODUCT(--(TEXT(A2:A10000,"mmmyyyy")="Dec2011"),E2:E10000)

If not, then what about 20000 rows?

=SUMPRODUCT(--(TEXT(A2:A20000,"mmmyyyy")="Dec2011"),E2:E20000)

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.

True, you do have a point there, but as I said in a previous post, I found a different formula that is actually more flexible and uses a simple SUM and IF functions. The only reason I was looking for previous version compatibility was that Polaris Office can only write in 2003 version (though it can read 2010 files just fine), but now that I am using QuickOffice instead, I have eliminated that problem.