4 threads calculating. Massive slowdown since adding sumifs formulas

[Johnny247]

Hi all,
I have a file I use for keeping track of booking vehicles.
I also have a tab (statistics) that I will use for trending vendor performance.
There is 4 sets of criteria (early, late, on time and Not arrived) for each haulier/vendor, for each week of the year.
I am using a sumifs formula in each cell to calculate this, but the more I have add, the slower the file became with the 4 threads calculating message.
Is there another formula or another way of achieving the same result?
Any help would be appreciated.

[Glenn Kennedy]

Do you REALLY need to see all of the weeks data at once?

Two suggestions. Use a dropdown box to select the week you're interested in and do a COUNTIFS on that one only. Or... restrict the COUNTIFS range. COUNTIFS normally doesn't mind whole column references, but you do have a shedload of whole column references there!!!!

[Johnny247]

Hi Glenn,
thanks for the reply.
Unfortunately, I do need the weeks as it is used to calculate haulier performance and I would pull out haulier by year. I thought I had restricted the range?
Is there a way do you think that I could work around it with VB instead?
The only other option that I thought of, was simply set the statistics tab up on a separate file and just link the formulas to that file. The stats file would be really slow I guess, but at least the booking in file would remain quick and useable.
Thanks again.

[Glenn Kennedy]

Ok... so do you really need to see all the hauliers at once? Dd for haulier and then return data???? I can set it up, if a possibility.

[Johnny247]

Ahh! Now that's a possibility.
No, I don't need to see all the hauliers at once (just capture the data). If you go to the far right of the stats tab you will see it is set up with a drop down list for hauliers to generate a graph with the output of the data. That is set up for one haulier at a time. That is all I am looking for really. If you can set something up with that, that would be great. Thanks.

[Glenn Kennedy]

Will do. Out for lunch now, but will set it up later on today.

[Glenn Kennedy]

Box A2: select a haulier (only Turners & Reed Boxall have had dummy data added by me).

Formula used in B4, copied across and down was:

=IFERROR(SUMPRODUCT(((INDIRECT("'"&$A4&"'!J3:AJ60")=Statistics!$A$2)*((INDIRECT("'"&$A4&"'!I3:AI60")=Statistics!B$3)))),"")

Voila.

However, it could be made MUCH more efficient if all the raw data was stored on a SINGLE sheet, with an additional column for week number. However, even as it is, it's faster (by a million miles) than previously.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[Johnny247]

That's perfect! Thanks very much sir!