Maxifs

[JoeExcelHelp]

Trying to avoid using an array and was hoping this could be modified.. need to add 2 more conditions
=MAX(INDEX((A2:A10=D2)*B2:B10,0))

[HansV]

Depends on what you want, but perhaps something like this:

=MAX(INDEX((A2:A10=D2)*(C2:C10>37)*(MONTH(E2:E10)=7)*B2:B10,0))

C2:C10>37 and MONTH(E2:E10)=7 are the extra conditions.

[JoeExcelHelp]

This is my actual condition but it returns "#Value"

=MAX(INDEX((Schedule!B:B=AE5)*(Schedule!D:D=D1)*Schedule!F:F,0))

[HansV]

In the first place, I wouldn't use entire columns. Since each column has 1,048,576 rows in a .xlsx/.xlsb/.xlsm workbook, the number of multiplications is very large, which will have a negative effect on performance. It's better to use limited ranges, as in your first post.

In the second place, do any of the columns referred to contain error values? That would cause the formula to return an error too.

[JoeExcelHelp]

Your suggestion worked thanks Hans

[JoeExcelHelp]

Hi Hans
Trying to return the max and min value based on a range within a table and not having any luck at it

=MIN(INDEX(Schedule!$D$6:$IK$324,MATCH($D$1,Schedule!$C$6:$C$324,0),MATCH(AE$5,Schedule!$D$4:$IK$4,0)))

[HansV]

MATCH($D$1,Schedule!$C$6:$C$324,0) returns a single row number, and MATCH(AE$5,Schedule!$D$4:$IK$4,0) a single column number. So INDEX(Schedule!$D$6:$IK$324,MATCH($D$1,Schedule!$C$6:$C$324,0),MATCH(AE$5,Schedule!$D$4:$IK$4,0)) is a single cell. Taking the MIN or MAX of a single cell is fairly useless.

What exactly are you trying to do?

[JoeExcelHelp]

trying to exctract the min value within a range based on a column and row condition for example:
the range would be defined by anything that matches BB (row) and XX (col) with a min of 1 max 3
______aa bb bb bb bb cc cc
__XX 1 1 1 2 3 1 1

[HansV]

As an array formula, confirmed with Ctrl+Shift+Enter:

=MAX(IF(Schedule!$C$6:$C$324=$D$1,IF(Schedule!$D$4:$IK$4=AE$5,Schedule!$D$6:$IK$324)))

and

=MIN(IF(Schedule!$C$6:$C$324=$D$1,IF(Schedule!$D$4:$IK$4=AE$5,Schedule!$D$6:$IK$324)))

[JoeExcelHelp]

Thanks again Hans