Microsoft has made some great ‘under the hood’ improvements to the combo ‘IF’ and ‘IFS’ functions like SumIFS(), SumIF(), CountIF() to make them much faster than before.
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:
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
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 Insiders v 2007 build 13029.20200