How to get max number from a column, based on a specific criteria from another column

Michael Giovanni

How to get the green colored cell based on the right table by using formula?

Joanna Waligora

Hi,

It would generally be advisable to separate the adjective and the subject into two columns i.e. have two columns one named "Colour" and another named "Species", rather than a single "Animal" column.

It's also helpful to use singular form in the "Animal Types" column i.e. cat, dog. This allows for referencing these cells in the calculation, since they're exactly the same as the values in the "Species" column.

If you can make that happen then the following formula should work:

=MAX(COLLECT([Number]:[Number], [Species]:[Species], [Animal Types]@row))

If cleaning up the source data is not an option, you can look at embedding a search function into the above, so:

=MAX(COLLECT([Number]:[Number], [Species]:[Species], CONTAINS([Animal Types]@row, @cell)=1))

In both of the above, if you want to keep the plural values in the "Animal Types" column, you can just replace the "[Animal Types]@row" chunk with desired species so either "dog" or "cat".