Sunday, July 1, 2012

yogi_Setup Formula For Multi_Condition Sum That Works In Google Spreadsheet As Well As In Excel And OpenOffice Spreadsheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #603   Jul 1, 2012

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 ( 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. 
The formula I'm using is:
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. 
You can see the spreadsheet here:
Thank you for any help you can offer.
in the following solution to the problem, in Sheet1 I have set up the formula for multi_conditional sum that works in Google spreadsheet, as well as in Excel and Open Office spreadsheet