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.
The reports could use either SUMPRODUCT or SUMIFS to grab the data. I’ve always had the feeling that SUMIFS was faster than SUMPRODUCT, but here was a reason to test that thought.
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.
