# Array formula question

Posted by Andonny on June 18, 2001 2:59 AM

Hi,
I am using the array formula below but it is causing me a bit of trouble because it takes about 45 minutes to calculate one sheet. Is there an alternative formula I could use to speed up my calculating process.

=SUM((Sheet1!\$B\$1:\$B\$20000=\$A5)*(Sheet1!\$E\$1:\$E\$20000=D\$1)*(Sheet1!\$D\$1:\$D\$20000))

Thank you very much for your help
Andonny

Posted by Aladin Akyurek on June 18, 2001 4:16 AM

Andonny,

Take a 4 free columns where you do the calcs:

In F1 enter: =(Sheet1!B1=\$A\$5)+0
In G1 enter: =(Sheet1!E1=\$D\$1)+0
In H1 enter: =F1*G1*Sheet1!D1
Select F1:H1 and copy down up to the row 20000.
In I1 enter: =SUM(H:H)

Cheers,

=====================

Posted by Aladin Akyurek on June 18, 2001 6:03 AM

Make that a single col computation

:Andonny,

What I suggested can be done in a single column:

In F1 enter: =((Sheet1!B1=\$A\$5)+0)*((Sheet1!E1=\$D\$1)+0)*(Sheet1!D1)

Copy down this up to the row 20000.

Then of course in G1:

=SUM(F:F)

This must be cheaper than 3-column version.

=====================

Posted by Andonny on June 18, 2001 6:05 AM

Re: Sorry some difficulties

Hi,
I am having a bit of difficulties with the recommentation. I am not sure if my formula is clear enough. It is in sheet2 in column D.

Thanks
Andonny

Posted by Aladin Akyurek on June 18, 2001 6:20 AM

Re: Sorry some difficulties

Hi,

Use a unused column on Sheet2. I'd suggest implementing the single column version I suggested below in the thread. That is:

=((Sheet1!B1=\$A\$5)+0)*((Sheet1!E1=\$D\$1)+0)*(Sheet1!D1)

Copy down as far as needed (total 20000 rows). Then do a sum on the resulting range.

I'm terribly curious about the results qua time spent doing calcs.

================

Posted by Andonny on June 19, 2001 2:52 AM

Re: May I send the sheet