# Thread: To Array or Not to Array (97; Sr2)

1. ## 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. ## 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. ## 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. ## 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
•