Multiply 2 Vectors With Booleans and Numbers and Take The MAX as Result

[rkaczano]

The formula below is attempting to multiply Booleans in $D$30:$D$33 with numbers in AD30:AG30. And I want to take the MAX of that result. However I am getting a funny answer and the MAX seems to be taking the highest value in AD30:AG30 irrespective of the Booleans.

The problem is that there is nothing implicitly multiplying the two arrays together. And a SUMPRODUCT does not work as I would need to calculate the MAX prior to the summing. I also tried an MMULT but could not get this to work either.

Any idea how to refine this.

=MAX(IF($D$30:$D$33=TRUE,AD30:AG30))

 

[Eric W]

Try:

=MAX(FILTER(AD30:AG33,D30:D33=TRUE))

 

[rkaczano]

Great thanks!