Microsoft has made some ‘under the hood’ improvements to the combo ‘IF’ and ‘IFS’ functions like SumIFS(), SumIF(), CountIF() to make them faster than before. Just don’t believe Microsoft’s excessive boasts.
The functions themselves behave the same but they now have a cached index for the range. That index is retained for any later combinations involving the same range (which is often the case). Obviously, the cache is updated if the cell values change.
The functions that benefit are:
SUMIF SUMIFS
AVERAGEIF AVERAGEIFS
COUNTIF COUNTIFS
MAXIF MAXIFS
MINIF MINIFS
The first function might take longer because it must create the index. It’s the later functions using the same range that get the benefits. That will help because IF/IFS functions are normally used together to show various summaries of the date (average, high, low etc).
Calculations are noticeably faster – really?
According to Microsoft, 1,200 functions working on a 1 million cell range once took 20 seconds to process now does the same job in 8 seconds.
That sounds impressive until you notice they are comparing Excel 2010 with Excel 365 instead of Excel 365 before and after the updated functions.
No doubt these changes are good, especially for large tables. But unfair comparisons with decade-old software and claims the ‘effect is dramatic’ aren’t really necessary.
These changes were introduced in Excel for Windows back in late 2020.
Excel Function Keys explained
SUMIFS Excel SUM with filters galore
More linked data types coming to Excel 365
SLK files used to hack into Excel, again
