Today, I learned about an Excel reporting need that looks something like this:
- There’ll be about 150,000 rows of data maintained in an Excel database.
- There’ll be dozens of reports, each with hundreds of different filter settings. (Different products, divisions, etc.)
- The data will update monthly.
So I set up a test database with 150,000 rows and three columns. Two of the columns contained data for filters. And the third contained amounts. Then I created 500 formulas to return values from this data.
(For comparison to your computer, I’m using an Intel Core2 Quad CPU Q8399 running at 2.50 GHz.)
Here’s what I found:
- When I used 500 SUMIFS formulas, each referencing those 150,000 rows, they took an average of about 2.7 seconds to calculate.
- When I used 500 SUMPROUDCT formulas to return the same results, they took about 13.2 seconds to calculate.
So the SUMPRODUCT version took nearly five times longer than SUMIFS.