Array Max If And

[ron_oniel]

I don't full understand the array formulas but would like to get the maximum of a certain column based on a match criteria as well as a less than greater than criteria.

I want to do something like:
{max(and(Customer List=Particular Customer,Range>20,Range<20),Range,"")}

but I guess it doesn't work like that?

[NBVC]

Not sure I follow that second table... so why is the first column blank? and the other blanks too? why?

[ron_oniel]

The blanks are there on purpose to show there is no value that meets the criteria. So the table with the blands is what I want. The table above it (second table) was used as an intermediate. I would rather not make an intermediate table to get what I want.

I'm hoping I could use multiple criteria for the array formula not just the one IF criteria shown in the intermediate (second) table.

[NBVC]

I guess I am confused about what is that secondary criteria, that should make those cells go blank.

[ron_oniel]

The IF statements in the last (3rd) table are the secondary criteria. If the number doesn't fall within a range, the cell is blank ("").

I want to get the maximum value of a certain column of values between a certain number range along with a match criteria.

Table 2 has everything except the number range criteria hence the IF statement in table 3.

[NBVC]

Should the first column be totally blank? Are you looking for values between 0 and 20 there?

Try in H14:

Please Login or Register  to view this content.

CSE confirmed and copied across the matrix.

[ron_oniel]

That was exactly what I needed! I need to spend some time looking at why my way didn't work and yours does. I think it has to do with the sequence of the functions you did (i.e. IF, then AND, then MAX versus MAX, IF, AND). Awesome and well done!

How do I add [SOLVED] to the thread title?

[Haseeb Avarakkan]

Also,

H3, copy across & down

Please Login or Register  to view this content.

CSE formula. Confirmed with Control+Shift+Enter, rather than just Enter