Redeem the largest number of a matrix with criteria

Question

I need some formula or logic to rescue the largest number of a matrix, but with certain criteria.

As well as the formulas CONT.SES and SOMASES that counts and sums the numbers of a matrix when the criteria are 'met'.

Something that would work as a MAIOR.SES(IntervaloApuração;K;IntervaloCriterio1;Criterio1;...)

A simple example of how it would work:

+---+-------+-------+------------+
|   |   A   |   B   |     C      |
+---+-------+-------+------------+
| 1 | Data  | Ativo | Quantidade |
| 2 | 26/07 | SIM   | 35         |
| 3 | 26/07 | SIM   | 39         |
| 4 | 26/07 | NAO   | 40         |
| 5 | 27/07 | SIM   | 13         |
+---+-------+-------+------------+

If I want the largest amount of the following criteria: data = 26/07 and Ativo = SIM the result would return me the amount of record 3 ie: 39

Someone with a solution?

Answer 1

Use the "MAXIMUM" function and within it use the "SE". However, you should be aware that the formula thus works for a single cell, for your case it is necessary to make it work with vectors, and for that there is a tool in Excel that is called Array Formulas.

To use it, it’s as simple as pressing three keys. Instead of pressing Enter, you will press Ctrl + Shift + Enter.

To be sure whether it worked or not, check whether the formula got a pair of keys before and after it, like this: {=MÁXIMO(SE(A2:A6=F2;(SE(B2:B6=F3;C2:C6))))}.

In your case, it would look like this:

Upshot:

But don’t forget the Ctrl + Shift + Enter by typing the formula into cell F6.

Answer 2

Even simpler, just use the formula MAXIMIMOSES, which does exactly what you want:

MÁXIMOSES(IntervaloApuração;IntervaloCriterio1;Criterio1;...)