How to optimize COUNTIFS with very large data

0
votes

I would like to create a report that look like this picture below.
My data has around 500,000 cells (it will continue to grow larger)

Right now, I'm using countifs function from excel but it takes a very long time to calculate. (cannot turnoff automatic calculate)

The main value is collected as date and the range of date is about 3 years, so I have to put a lot of formula to cover all range of value.

resultresult

The picture below is the datasource the top one cannot be changed. , while the bottom is the one I created by myself (can change). I use weeknum to change date to week number.

datadata

Are there any better formula or any ways to make this file faster? Every kinds of suggestions are welcome!
I was thinking about using Pivot Table, but I don't know how to make pivot table from this kind of datasource.
PS. VBA is the last option.

You can download example file here: https://www.mediafire.com/?t21s8ngn9mlme2d

2
exceloptimizationexcel-formulalarge-data
What is a "very long time"? - Tim Williams
I mean the calculation time. Since I have to create 2 levels formula (1 to convert date to weeknum, another is to count weeknum with conditions) - Krsnik195
I meant how long does it actually take - Tim Williams
Oh...it's about 5-10 minutes depends on computer. But it's very annoying because every time I filer data, the calculation starts. And I cannot disable auto calculate function because it'll ruin other parts of document. So, I want to reduce formula or use other formula or use pivot table to improve its performance. - Krsnik195
@Krsnik195 What about disabling autocalculation just for the time when you are entering new data; then re-enable after? - Ron Rosenfeld

2 Answers

2
votes

I will post this answer with the disclaimer that it is entirely dependent on the size of the data set. That turning on and off the auto calculate is the best way, but your question doesn't let me do that, so keep reading.

Your question made me curious, so I gave it a try and timed it. I essentially set up two columns of over 100,000 rand numbers choosing from 1-1000 and then tried to do a countif on the two columns if they were equal. I made a macro that I can run that turns off the autocalculate, inserts the start time, calculates, and then inserts the finish time. I highlighted in yellow the time difference.

First I tried your way, two criteria, countifs: enter image description here

Then I tried to combine (concatenate) the two columns to see if I could make it easier by only having one countif criteria and data set. It doesn't. see result below:enter image description here

Finally, realizing what was going on. I decided to make the criteria only match the FIRST value in the number to look for. I was essentially reducing the number of characters to check per cell. This had a positive result. See below: enter image description here

Therefore my suggestion is to limit the length of the words you are comparing in anyway possible. You are mostly looking at dates, so you might have to get creative, but this seems to be the best way possible without going to manual calculation.

1
votes

I have worked with Excel sheets of a similar size. Especially if you are using the data on a regular basis, I would heartily recommend switching to a proper database SQL based, Access, or whatever fits your purpose. I does wonders for the speed and also you won't run into the size limits of Excel. :-)

You can import the data you have now fairly easy. I am happy as a clam with my postgresql db.