With almost universal availability of Internet and the availability of reliable on-line productivity tools, such as Google Docs, individuals as well as companies are switching over to Cloud Computing. In this blog I will post items of interest to my colleagues/patrons/clients.
Sunday, July 1, 2012
yogi_Setup Formula For Multi_Condition Sum That Works In Google Spreadsheet As Well As In Excel And OpenOffice Spreadsheet
user smithbc said: Is there an Array or Sumifs formula that will work in a Google Spreadsheet and in Open Office / Excel? MAC OSX - 10.6.8
Chrome - Version 20.0.1132.47
Open Office 3.3.0
I am creating a budget spreadsheet that I would like to be used online and offline. I have software that allows me to sync my spreadsheets with Google Docs and edit them online or offline (www.insynchq.com). Ultimately what I want to do is allow users of the spreadsheet to edit online and offline, but it appears that you cannot edit a google spreadsheet offline, AND it appears that Open Office and Google Spreadsheets speak different formula languages when it comes to complicated formulas.
On my computer I am using Open Office 3.3.0. I am trying to create a SUMIFS or similar formula to run some calculations. I have found the Google Spreadsheet formulas that are alternatives to SUMIFS and implemented those successfully. However, when I download the Google Spreadsheet into Open Office, I get errors where the formulas are. I would like to know if there is a formula that both Google Docs and Open Office and Excel would recognize.
What the formula is doing is looking for a date (G10) in a list of expense entries by date (A3:A). It is also looking for a category (H9) in the same list of expense entries (D3:D) and when those two match it is summing the amounts (E3:E) of the expenses that meet those criteria.