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     www.energyefficientbuild.com.


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. 
The formula I'm using is:
=IFERROR(IF($G10<>"";SUM(FILTER($E$3:$E;$A$3:$A=$G10;$D$3:$D=H$9));0))
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


Posted by at
Labels: , , , , ,

9 comments:

  1. Sailing Smitty (Brandon Smith)July 1, 2012 at 9:17 AM

    Thanks Yogi. If I want the zero values to show as blank cells, how would I modify the formula? I did a bunch of searching around and could not find an easy way to do this, especially with a formula this complicated. Thanks again!

    ReplyDelete
  2. Yogi AnandJuly 1, 2012 at 9:25 AM

    Hi Brandon Smith:

    I have updated the formula in Sheet2 in my blog post.

    Let me know how it works out for you.

    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete
  3. Sailing Smitty (Brandon Smith)July 1, 2012 at 12:46 PM

    Yogi,
    Sorry I missed that the first time. You are truly a master! Thank you!

    ReplyDelete
  4. Sailing Smitty (Brandon Smith)July 1, 2012 at 1:14 PM

    Yogi, In Open Office, this spreadsheet is running extremely slow - Is that due to the formula? Is there anything to do about it?

    ReplyDelete
  5. Yogi AnandJuly 1, 2012 at 1:28 PM

    Hi Brandon:

    Sorry, I don't use Open Office much ... so can't really say what is causing the spreadsheet to run extremely slow -- however recall that the formula(s) I gave you are long because I had to make them work with Open Office.

    If you get some information, please do share with me -- Thanks.

    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete
  6. Sailing Smitty (Brandon Smith)July 1, 2012 at 2:09 PM

    Hi Yogi,
    I also tested it on a friends computer in Excel and it has the same problem - very slow even when entering simple date like dates and text. I wonder if the formulas when copied across thousands of cells are just too much. In Google Spreadsheets it's actually fine.

    One more request if you would indulge me.
    Could you give me just the formula for Open Office in two variations - one where the cells with "0" value is blank and another where "0" value is left at "0" just in case that first formula is too heavy.

    The people using this are out in the field, often without access to internet, so I think I'll just have them use Open Office, which they have, and save that file to a server (GDrive) without having the option to edit the file. They're also on netbooks which don't have the most processing power, so I need to spreadsheet to be as light as possible.

    Thank you again for your generous support. I'm doing this as a favor for a really wonderful group, but got in a bit over my head!

    ReplyDelete
  7. Sailing Smitty (Brandon Smith)July 2, 2012 at 1:29 PM

    Yogi,

    I just wanted to tell you that I figured out what was slowing down the spreadsheet. The range that the formulas were looking for was to large. In the Google Spreadsheet I put (A8:A) and this converted over the Open Office as (A8:A65000). When I changed it to (A8:A2000) everything started working much faster. Thanks so much for your help.

    ReplyDelete
  8. Yogi AnandJuly 3, 2012 at 9:20 AM

    Hi Brandon:

    Thanks for the update ... very helpful information.

    Cheers!
    Yogi

    ReplyDelete
  9. Yogi AnandJuly 4, 2012 at 8:33 AM

    Hi Brandon:

    In my last comment, I should have added ...
    so, it is even more important that instead of using open-ended range we better use a dynamic range using in the formula reference to only as many rows as are needed.

    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete

Subscribe to: Post Comments (Atom)