Sumproduct severely slowing down spreadsheet

    I'm an independent contractor and I have to bill per hour per property and specify the billing code within each property. This way, when I send an invoice the client can see how many hours I have spent on each type of job for each property. The spreadsheet I have created works, but is very sluggish with all the SUMPRODUCT formulas.


    I have attached a very small sampling of this spreadsheet and have highlighted in yellow the sumproduct formulas. Can anyone help me figure out a more efficient formula that doesn't slow my spreadsheet time so much?


    Thanks,

    Re: Sumproduct severely slowing down spreadsheet


    I would love to use a pivot table, but I couldn't figure out how to set it up for this.

    Re: Sumproduct severely slowing down spreadsheet


    How many rows are you referencing with the SUMPRODUCT formulas in your real data?


    It may be that OFFSET is contributing to the inefficiencies - OFFSET is a "volatile" function which means that it recalculates every time something is changed even if data directly affecting that formula doesn't change - INDEX is better, e.g. in C19 copied down


    =SUMPRODUCT($M$5:$M$13,INDEX($G$5:$K$13,0,MATCH($A19,$G$4:$K$4,0)))


    and similar for your other formulas

    Re: Sumproduct severely slowing down spreadsheet


    Daddylonglegs - You're awesome. I am still going to try some pivot tables and such to see if I can get something that works, but this sped my spreadsheet up a lot! Good to know about OFFSET.


    Thanks!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login