Large Database - slow calculations

[opinions4u]

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.

 

[storm8]

Maybe pivot table?
https://www.google.com/search?hl=en&q=pivot+table+excel

I think generally storing dates would be faster in rows as excel is designed to have distinct records in rows and their attributes in columns. If this is your case...

 

[opinions4u]

Thanks. Unfortunately my data source is a big pivot table where I need to add fresh data onto a bigger database. So some calculations are essential. I think!

 

[J.Ty.]

I have two questions:

1) Are there any duplicate values in the range $D$15:$D$1600?
2) Are there any duplicates in the range $E$5:$BZZ$5?

Because you are selecting data from a pivot table, I suspect that the answer in both cases is "NO".

If I'm right, then the following formula will be a faster equivalent of your SUMPRODUCT:

INDEX('SALE1'!$E$15:$BZZ$1600,MATCH(fax!BAE$2,'SALE'!$D$15:$D$1600,0),MATCH(fax!$A3,'SALE1'!$E$5:$BZZ$5,0))

If I'm wrong and there are duplicates there, please let me know where and if you could afford sorting your data.

Best,

J.Ty.

 

[opinions4u]

1) Yes. This is where dates live and there are a range of different types of customer for different dates stored in column C for future inclusion. If there's value to me losing the column C data that I've added to the original pivot I could do this.

2) Yes, because I've added some formula to the pivot to make the SUMPRODUCT pick out the data I need. The pivot has around 5 or 6 headings in the rows below. Rather than just have blank values I've put in a formula to repeat the heading below until it changes.

A further thought. If I used multiple pivots with an extra filter in would this make my data calculate faster? No trip across to BZZ! A bit more copy and paste for me though.

 

[J.Ty.]

Well, so the next question:

If I understand you correctly, each unique value in the range $E$5:$BZZ$5 occupies always a contiguous range of cells. Am I right?

J.Ty.

 

[opinions4u]

I tell a lie. E to BZZ is my concatonation to is a range of unique cells (except for those that over-run the end of the data sheet).

 

[J.Ty.]

So, if E to BZZ does not contain duplicates, please test the following formula:

=SUMIF('SALE'!$D$15:$D$1600,fax!BAE$2,INDEX('SALE1'!$E$15:$BZZ$1600,,MATCH(fax!$A3,'SALE1'!$E$5:$BZZ$5,0)))

I apologize for not testing it myself, but I do not have access to my Excel at the moment. Therefore please do the tests very carefully.

J.Ty.

 

[opinions4u]

Thanks to all who replied.

All suggestions have worked well. Impacting a lot of my work.

 

[J.Ty.]

Thanks for the feedback and good luck!

J.Ty.