Why is My MAXIFS Alternative Not working?

[rob51852]

Hi,

I don't have the MAXIFS function in my version of Excel so I am trying to achive the same result using an array.

I thought this would work but it just gives an error when entered with CSE. I am trying to find the max value in LP where the criteria are met.

=MAX(IF(DataTable[Date]>E8,IF(DataTable[ID]=A8,IF(DataTable[Name]=C8),DataTable[LP])))

Can anyone see the problem?

Thanks

 

[jasonb75]

What error are you seeing? The formula looks like it should work so the most likely cause would be an error in the table is being carried through to your formula.
If that is the case then the preferable fix would be to correct the errors at the source although this formula would work as long as at least one row meets the criteria, otherwise it will show a #DIV/0! error which can be suppressed with IFERROR.

=AGGREGATE(14,6,DataTable[LP]/(DataTable[Date]>E8)/(DataTable[ID]=A8)/(DataTable[Name]=C8),1)

 

[rob51852]

Thanks Jason