Hello,
I have a workbook that has a large amount of SUMPRODUCT formulas to sum a column that meets certain criteria in 2 other columns. The workbook is very slow to recalculate.
I believe that 2007 & 2010 offers an alternative, SUMIFS ?
Would switching from SUMPRODUCT to SUMIFS improve performance at all? Are there other pro's / con's?
SUMPRODUCT v SUMIFS
-
VegasNath
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
SUMPRODUCT v SUMIFS
Nathan 
There's no place like home.....
-
HansV
- Administrator
- Posts: 78518
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: SUMPRODUCT v SUMIFS
Yes, Excel 2007 introduced new functions SUMIFS, COUNTIFS and AVERAGEIFS that let you sum, count or average a range with mutiple conditions. These new functions are MUCH faster than the sumproduct/array function versions from Excel 2003 and before.
See this somewhat long video: YouTube - Excel Formula Efficiency 4: SUMIFS & COUNTIFS Are Fast
See this somewhat long video: YouTube - Excel Formula Efficiency 4: SUMIFS & COUNTIFS Are Fast
Best wishes,
Hans
Hans
-
VegasNath
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: SUMPRODUCT v SUMIFS
98% faster seems like a good enough reason to switch! 
Nathan There's no place like home.....