#### opinions4u

##### New Member

- Joined
- Jan 30, 2009

- Messages
- 15

I have successfully used SUMPRODUCT formula for some time, but as I deal with more data I'm finding that calculations are taking longer and longer.

I'm working on a relatively new laptop with modern processing speeds, so that's not the issue.

Typically I have a range of data running from cell A to BZZ and down 1,500 rows or so. I restrict my formula to these cells.

I needs to read chunks of data from within multiple cells under 5 different headings and dates. I've tried using CONCATONATE to assist with faster calculations - the SUMPRODUCT only needs to read the data and concatonated cell name - but this doesn't seem to have made an obvious difference.

The formula that is taking its time is =SUMPRODUCT(('SALE'!$D$15:$D$1600=fax!BAE$2)*('SALE1'!$E$5:$BZZ$5=fax!$A3)*('SALE1'!$E$15:$BZZ$1600))

Sale1 is my source data.

fax is my output data (added to a growing data base)

I'm assuming it's the size of the datasheet heading out to BZZ that's my problem.

1) Is there a better formula to use, or a more efficient way to read the data?

2) What's the best way to store dates (e.g. in a column, in a row or no difference)?

Thanks.

I'm working on a relatively new laptop with modern processing speeds, so that's not the issue.

Typically I have a range of data running from cell A to BZZ and down 1,500 rows or so. I restrict my formula to these cells.

I needs to read chunks of data from within multiple cells under 5 different headings and dates. I've tried using CONCATONATE to assist with faster calculations - the SUMPRODUCT only needs to read the data and concatonated cell name - but this doesn't seem to have made an obvious difference.

The formula that is taking its time is =SUMPRODUCT(('SALE'!$D$15:$D$1600=fax!BAE$2)*('SALE1'!$E$5:$BZZ$5=fax!$A3)*('SALE1'!$E$15:$BZZ$1600))

Sale1 is my source data.

fax is my output data (added to a growing data base)

I'm assuming it's the size of the datasheet heading out to BZZ that's my problem.

1) Is there a better formula to use, or a more efficient way to read the data?

2) What's the best way to store dates (e.g. in a column, in a row or no difference)?

Thanks.

Last edited: