Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    To Array or Not to Array (97; Sr2)

    Which would be faster in a calculation an 'array formula or a function" to sum columns based on an account.

    Example of the array formula: =SUM(('Balance Sheet'!C$13$50021=C9)*'Balance Sheet'!D$13$50021) In this case C9 equals the account number.


    I have a workbook that takes about one minute to calculate using this array formula and was wondering if there is a better way to reduce the calculation time.

    John

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: To Array or Not to Array (97; Sr2)

    Why not a SUMIF:

    <pre>=SUMif('Balance Sheet'!C$13$50021,C9,'Balance Sheet'!D$13$50021) </pre>


    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: To Array or Not to Array (97; Sr2)

    Try the following in a copy of the workbook to see if they recalculate faster; both are "normal" formulas (not array formulas):

    =SUMIF('Balance Sheet'!C13:C50021,C9,'Balance Sheet'!D1350021)

    =SUMPRODUCT(('Balance Sheet'!C13:C50021=C9)*'Balance Sheet'!D1350021)

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: To Array or Not to Array (97; Sr2)

    Steve,

    Thanks for the suggestion. The calc time was reduced to 6 seconds.

    Regards,
    John

Posting Permissions

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