CODEWITHSUNDEEP
excelmax
SteelyDanFan
SteelyDanFan

Reputation: 79

Finding a MAX value based on multiple criteria Excel

I have created a downloadable sheet in GoogleDrive to show my problem.

https://docs.google.com/spreadsheets/d/1O8fnWQyTFcsqfHAsmpiY4o-n2_xbC-yue2QI4Ww4kSI/edit?usp=sharing

I need to combine the following formula which finds the maximum value in H1:H1024 which is less than or equal to the value specified in B16 

    {=MAX(IF(H1:H1024<=B16,H1:H1024, 0))}

with a formula which adds the argument that the cell adjacent to the value must contain all the elements listed a range of cells D1:D10 - the following formula returns TRUE when that is the case

    =SUMPRODUCT(--ISNUMBER(SEARCH($D$1:$D$10,F1)))=COUNTA($D$1:$D$10)

The following does not work but illustrates what I am trying to achieve

    {=MAX(IF(AND(H1:H1023<=B16,E1:E1023="TRUE"),H1:H1023, 0))}

where E1 contains the SUMPRODUCT formula dragged down to E1023.

In know a macro would be better for this (and would make dealing more items viable) but that is not an option for me.

Cheers in advance.

Upvotes: 0

Views: 3245

Answers (2)

SteelyDanFan
SteelyDanFan

Reputation: 79

@ScottCraner got there before me but you can also put "--" in front of a reference to the TRUE/FALSE column to force it to a numeric value (1 for TRUE, 0 for FALSE) like this

    =MAX(IF(H1:H1023<=B16,IF(--E1:E1023=1,H1:H1023)))

but as @ScottCraner points out, you can just leave the range as is so his answer should prevail as is less complex.

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152450

Try

=MAX(IF(H1:H1023<=B16,IF(E1:E1023,H1:H1023)))

It is an array formula so Ctrl-Shift-Enter.

Array formulas do not like the AND/OR functions. One must chain IF functions for each desired check.

The false option is not needed when using the IF statements this way.

Upvotes: 2

Related Questions