Alternative to array formula to get answer?

R

Rob Gould

Hello all,

I have an Excel database of around 5,000 rows. One of the fields
contains a date. I colate the data on a separate sheet into
categories for a specific date by using the following array fomula:

{=ROUND(SUM((DATA!$C$2:$C$5000="Daily
News")*(DATA!$B$2:$B$5000=B78)*DATA!$Z$2:$Z$5000),2)}

B78 contains the date required and column Z contains the revenues I am
wanting to add together for that particular date, in order to get a
single figure.

Is there any other way of adding together the revenues of a number of
records for a particular date, other than using the array formula
above. It just takes sooooo long to calculate!

Many thanks.

Rob
 
B

Bob Umlas, Excel MVP

You can try SUMPRODUCT instead of SUM - rest of formula stays exactly the same. No need to array-enter.
 
R

Ron Rosenfeld

Hello all,

I have an Excel database of around 5,000 rows. One of the fields
contains a date. I colate the data on a separate sheet into
categories for a specific date by using the following array fomula:

{=ROUND(SUM((DATA!$C$2:$C$5000="Daily
News")*(DATA!$B$2:$B$5000=B78)*DATA!$Z$2:$Z$5000),2)}

B78 contains the date required and column Z contains the revenues I am
wanting to add together for that particular date, in order to get a
single figure.

Is there any other way of adding together the revenues of a number of
records for a particular date, other than using the array formula
above. It just takes sooooo long to calculate!

Many thanks.

Rob
Click to expand...

I'd be interested to know if that particular formula is the reason for your
slowdown, and if changing it to a SUMPRODUCT equivalent really speeds it up.

I set up an array of values in B2:b5000,z2:z5000 and used your formula. The
computation was done by the time I lifted my finger of the <enter> key.

SUMPRODUCT does go a bit faster than the array method, but I'd like to know if
you note a significant change in your application.


--ron
 
R

Rob Gould

Ron Rosenfeld said:
I'd be interested to know if that particular formula is the reason for your
slowdown, and if changing it to a SUMPRODUCT equivalent really speeds it up.

I set up an array of values in B2:b5000,z2:z5000 and used your formula. The
computation was done by the time I lifted my finger of the <enter> key.

SUMPRODUCT does go a bit faster than the array method, but I'd like to know if
you note a significant change in your application.


--ron
Click to expand...

Ron....It is very slow when you have 6,500 array formulas! I have
used DSUM and DPRODUCT now and it is much quicker....thanks for the
post.

Rob
 
R

Ron Rosenfeld

Ron....It is very slow when you have 6,500 array formulas! I have
used DSUM and DPRODUCT now and it is much quicker....thanks for the
post.
Click to expand...

SUMPRODUCT is supposed to be faster than the equivalent array formula.

I've seen no speed comparisons with the DB formulas, though. Thank you for
that information.


--ron
 
Top