• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumproduct Slowing down my file

E

Ecel Dumbo

Member
Hi All, I have used sumproduct formula in the attached file and it seems to be slowing down my file. I am aware of the work around steps like changing calculation to Manual, but i was wondering if there was a substitute formula that i can use, the spreadsheet will keep growing in size month by month
 

Attachments

  • Sum If Query.xlsx
    493.1 KB · Views: 0
The isssue isn't Sumproduct
It is that you are using the entire 1048576 Rows to search for the results when you actually have less than 5000 records

I would use Named formula defined as per the attached file:

These formula will automatically adjust based on the addition or removal of data in Column A

You could also use the Sumifs() function to do the same function as Sumproduct in this case

I would also remove all the named Formula that have #REF! errors
 

Attachments

  • Sum If Query.xlsx
    492.8 KB · Views: 3
You must log in or register to reply here.
Back
Top