If I write a formula using query and limit it to one result, I can then use it as a number, for instance

=1+QUERY(B13:F18,"SELECT C WHERE (B='Banana') LIMIT 1")

But if I want to use the maximum value the same way, it gives me an error:

=1+QUERY(B13:F18,"SELECT MAX(C) WHERE (B='Banana')",0)

Function ADD parameter 2 expects number values. But 'max' is a text and cannot be coerced to a number.

Column C only contains numbers! If I exclude the addition I see that it is adding an unwanted label "max" above the max value. I am putting 0 for the third parameter to query, which I thought meant I wouldn't have any header lines. I also tried "" and that didn't work either.

I thought maybe it was treating the result as an array, so I wrapped the whole thing with:

=INDEX(QUERY(...), 0, 1)

Which I expected to skip over the row containing max. But I got the exact same output!

How do I extract just the number so I can use it in the formula?

  • You need specifically QUERY? If not, you may use MAXIFS(C:C,B:B,"Banana")
    – Oleg_S
    Sep 2, 2020 at 6:53

1 Answer 1


I'm not sure if there is a better way to do it, but this works:

=1+QUERY(B13:F18,"SELECT MAX(C) WHERE (B='Banana') label MAX(C) ''",0)

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.