I want to exclude specific rows in array commands in google sheets.

Here is some sample data:

Exercise type Weight Unit Reps Success?
OHP 40 kg 4 yes
Deadlift 240 kg 2 no
OHP 50 kg 10 yes
Squat 110 kg 12 yes
OHP 60 kg 5 yes
Squat 100 kg 6 no
Squat 90 kg 20 yes
Deadlift 200 kg 10 yes
Deadlift 220 kg 14 no

I can use the command =ArrayFormula(MAX(IF($A$2:$A$999=A2,$B$2:$B$999))) to create the variable PR Max Weight, but I want to use the variable Success? (column E) to filter the command for the variable PR Max Weight such that rows where Success? == "yes" are included in the array, and rows where Success? == "no" are NOT included in the array.

Is this possible? If so, how can it be done? Thanks.

1 Answer 1


Use filter(), like this:

=max( filter(B$2:B, A$2:A = A2, E$2:E = "yes") )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.