_xlfn.MAXIFS error - Trying to recreate the formulas

Hi guys

I am getting _xlfn.MAXIFS - Which ive found to mean that my friend made this spreadsheet in the new office version!

So unfortunately since I am on 2016 I am stuck as this feature is not supported on my office!

Anyways - I am trying to get some advice on his formula and how I can fix it / make it work on my version of office!

Again any advice would be awesome!

Hi,

You can use a MAX(IF( formula instead of MAXIFS. For example this:

=MAXIFS($A$14:$A23,$D$14:$D23,$D24)

Can be changed into something like this :

=MAX(IF($D$14:$D23=$D24,$A$14:$A23))

Array Entered.

Hi

Thanks for that

So for example H24 would become this??

Please Login or Register  to view this content.

you can use
=AGGREGATE(14,6,$A$14:$A23/($E$14:$E23=$D24),1)
it is not array formula

Also, your VLOOKUPs are broken:

VLOOKUP((MAX(MAX(IF($D$14:$D23=$D24,$A$14:$A23)),MAX(IF($E$14:$E23=$D24,$A$14:$A23)))),$A$14:$I23,10)
and
VLOOKUP((MAX(MAX(IF($D$14:$D23=$D24,$A$14:$A23)),MAX(IF($E$14:$E23=$D24,$A$14:$A23)))),$A$14:$I23,11)

But you range is from A to I so you have only 9 column so you will get a #REF! error. Investigate them first

Hi again, ive managed to get it going using this.

=IF((VLOOKUP((MAX(MAX(IF($D$17:$D26=$D27,$A$17:$A26)),MAX(IF($E$17:$E26=$D27,$A$17:$A26)))),$A$17:$K26,4))=D27,(VLOOKUP((MAX(MAX(IF($D$17:$D26=$D27,$A$17:$A26)),MAX(IF($E$17:$E26=$D27,$A$17:$A26)))),$A$17:$K26,10)),(VLOOKUP((MAX(MAX(IF($D$17:$D26=$D27,$A$17:$A26)),MAX(IF($E$17:$E26=$D27,$A$17:$A26)))),$A$17:$K26,11)))+L27

So one COLUMN is working, I will work on another soon, however now it is running very slow - Would Aggregate work faster?

There is only one way to know. Use it in the formula. Probably it will be faster.

Good point! Do you still use the VLOOKUP then the AGGREGATE(14,6,$A$14:$A23/($E$14:$E23=$D24),1) ??

Yes, the aggregate formula posted by tims can replace these 2 formulas:

=MAXIFS($A$14:$A23,$D$14:$D23,$D24)


=MAX(IF($D$14:$D23=$D24,$A$14:$A23))

Thanks Paul you've been great

So this is where im up to now - Ive changed it and I get a #NUM! error

Thought 14,6 was meant to ignore that lol


=IF((VLOOKUP((MAX(AGGREGATE(14,6,$A$17:$A26/($D$17:$D26=$D27),1),AGGREGATE(14,6,$A$17:$A26/($E$17:$E26=$D27),1))),$A$17:$K26,4))=D27,(VLOOKUP((MAX(AGGREGATE(14,6,$A$17:$A26/($D$17:$D26=$D27),1),AGGREGATE(14,6,$A$17:$A26/($E$17:$E26=$D27),1))),$A$17:$K26,10)),(VLOOKUP((MAX(AGGREGATE(14,6,$A$17:$A26/($D$17:$D26=$D27),1),AGGREGATE(14,6,$A$17:$A26/($E$17:$E26=$D27),1))),$A$17:$K26,11)))+L27

=IF((VLOOKUP((MAX(MAX(IF($D$17:$D26=$D27,$A$17:$A26)),MAX(IF($E$17:$E26=$D27,$A$17:$A26)))),$A$17:$K26,4))=D27,(VLOOKUP((MAX(MAX(IF($D$17:$D26=$D27,$A$17:$A26)),MAX(IF($E$17:$E26=$D27,$A$17:$A26)))),$A$17:$K26,10)),(VLOOKUP((MAX(MAX(IF($D$17:$D26=$D27,$A$17:$A26)),MAX(IF($E$17:$E26=$D27,$A$17:$A26)))),$A$17:$K26,11)))+L27

I've updated your formulas in row 27, marked in blue, but I am not sure if I understood your logic there. See attached.

Thanks PaulM100! I will stick with the MAX(IF...

As I could not get the Aggregate working (even tho it could be faster!)

Thanks again for your help - Everything seems to be working now that I have copied it onto the master - And thanks for doing the other cells I copied and pasted those as well!

no worries, glad to help. Please mark the thread as Solved if that took care of your problem. Thanks.