Redeem the largest number of an array with criteria

Navigation
5

I need some formula or logic to retrieve the largest number of an array, but with certain criteria.

Just like the CONT.SES and SOMASES formulas that counts and adds the numbers of an array 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 most of the following criteria: data = 26/07 and Ativo = SIM the result would return me the quantity of record 3 ie: 39

Someone with a solution?

    
asked by anonymous 28.07.2015 / 15:33

2 answers

9

Use the "MAXIMUM" function and use the "SE" function. However, you should be aware that the formula works like this for a single cell, so you need to make it work with vectors, so there is an Excel tool called Array Formulas .

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

To make sure it worked out right or wrong, make sure the formula got a key pair before and after it, like: {=MÁXIMO(SE(A2:A6=F2;(SE(B2:B6=F3;C2:C6))))} .

For your case, it would look like this:

  

Result:

  

But do not forget when you type the formula in cell F6.

    
28.07.2015 / 17:33
0

Simpler still, just use the MAXIMUM formula, which does exactly what you want: 

MÁXIMOSES(IntervaloApuração;IntervaloCriterio1;Criterio1;...)
    
31.10.2016 / 00:33
Modal display when submitting a form JSON for PHP via AJAX