Faster SumIFS coming to Excel 365 for Windows

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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:

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).

Faster SumIFS coming to Excel 365 for Windows - Faster SumIFS coming to Excel 365 for Windows

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

Excel Function Keys explained

More linked data types coming to Excel 365

SLK files used to hack into Excel, again

subs profile e1563205311409 - Faster SumIFS coming to Excel 365 for Windows
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address