+ Reply to Thread
Results 1 to 5 of 5

Converting arrayformula from Google Spreadsheet to Excel

  1. #1
    Registered User
    Join Date
    08-04-2010
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Converting arrayformula from Google Spreadsheet to Excel

    I have this formula I'm trying to transfer from Google Spreadsheet to Excel.
    =ARRAYFORMULA(sum(('Copy of Sheet 1'!C:C="Pirate Legacy")*('Copy of Sheet 1'!B:B=A1)*('Copy of Sheet 1'!A:A<'Control'!B$1+1)*('Copy of Sheet 1'!A:A>'Control'!$B$1-6)))

    What I want is a count of all the rows in in the worksheet 'Copy of Sheet 1' where:
    Column C (Name) = Pirate Legacy
    Column B (Type) = cell A1 on the current sheet (A text field)
    Column A (Date) is within one week of the report date (cell B1 on the 'Control' sheet). That is to say: less than the report date +1, but more than the report date -6.

    Right now, I just get a #NAME? error. Any help would be appreciated!

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Converting arrayformula from Google Spreadsheet to Excel

    If as implied by your profile you are using XL2003 then

    a) you will need to persist with an Array / SUMPRODUCT rather than use the likes of COUNTIFS introduced in XL2007

    b) you will need to avoid use of entire column references (A:A) given they are not permitted pre XL2007 in conjunction with the above

    For sake of performance in Excel you should keep your Array ranges as lean as possible - I can't vouch for the same in Google Spreadsheet as I don't use it.

    Array - confirmed with CTRL + SHIFT + ENTER (not just Enter)

    Please Login or Register  to view this content.

    SUMPRODUCT - confirmed with Enter as normal

    Please Login or Register  to view this content.
    SUMPRODUCT is not really any more efficient than the Array, however, it has one advantage in so far as it does not require special "Array entry" - ie can be confirmed with Enter alone.
    The above fact makes it a little more robust from an end users perspective in so far as should you edit the formula at any time you need not worry about having to remember to reset the array for it to work.

  3. #3
    Registered User
    Join Date
    08-04-2010
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Converting arrayformula from Google Spreadsheet to Excel

    2003 was the default entry for that field, and was not properly overwritten. I'm using 2007.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Converting arrayformula from Google Spreadsheet to Excel

    MS introduced some new functions with XL2007 - specifically COUNTIFS which is far more efficient than both of those functions outlined in my previous post
    (so much so that entire column references are not a huge issue)

    =COUNTIFS('Copy of Sheet 1'!C:C,"Pirate Legacy",'Copy of Sheet 1'!B:B,A1,'Copy of Sheet 1'!A:A,"<"&'Control'!B$1+1,'Copy of Sheet 1'!A:A,">"&'Control'!$B$1-6)

  5. #5
    Registered User
    Join Date
    08-04-2010
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Converting arrayformula from Google Spreadsheet to Excel

    That's working perfectly, thank you! I'm definitely looking forward to getting this document off Google Spreadsheet - it's very convenient, but not very reliable.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1