Why is Excel not able to compute MaxIfs function? (_xlfn. error)

Hi,

I'm using Microsoft Office Excel 2016 and have the following formula in 2 adjacent columns.

=VLOOKUP(MAXIFS('USD Account'!$B$6:$B$1048576,'USD Account'!$B$6:$B$1048576,"<=29/02/2016"),'USD Account'!$B$6:$H$1048576,7,TRUE)

However, no matter how many times I tried re-opening the file, it always shows as below:

=VLOOKUP(_xlfn.MAXIFS('USD Account'!$B$6:$B$1048576,'USD Account'!$B$6:$B$1048576,"<=29/02/2016"),'USD Account'!$B$6:$H$1048576,7,TRUE)

Why is "_xlfn." being prefixed to the formula? Even when I try deleting the "_xlfn." manually, it comes back.

I have tried re-installing Office 16, and also installed Office 13 as a last ditch attempt, but, even Excel 2013 gave me the same problem. 

Why is this happening? When I open the file via OneDrive, the Online Excel app reads my file perfectly and delivers the formula result without any problem. Can someone please help? Thanks for reading!

 

Question Info


Last updated August 16, 2019 Views 9,826 Applies to:
Answer
Answer

Hi, VelvetAssassin,

I can understand how that could be frustrating, but I'm glad you found a solution using INDEX and MATCH.

Access to new features is typically provided first to users with O365 accounts, then to users with perpetual licenses in later versions. From your comments it looks like you have a perpetual license, not O365, so MAXIFS shouldn’t have been offered yet.

In some uncommon cases, initial installation may take some time to validate your license (up to 5 days) and you may see new features in that period.  While this helps users get up and running faster, it can be confusing at times.  We're investigating possible ways to make the initial experience more predictable in the future.

Regards,

John

Microsoft Excel Team

1 person was helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.