If Statement - Max Value

[WorkMan]

I'm having trouble with writing an if statement to try and find the max value.

=IF(G31:G3000=E19,IF(H31:H3000=B22,IF(D31:D3000=B3,MAX(Q:Q),"")))

So the following will have a set value:

E19, B22, B3

And then Column Q will have all sorts of values. I want to find the max value of column Q if the previous 3 if statements are true.

I'm receiving #Value! when I try the above statement though. Any suggestions?

Thanks!

[modytrane]

That's an array formula.
Enter with Ctrl+Shft+Ent.
Hold down Control and Shift keys and hit enter.
Excel will add {} brackets around the formula.
You can't type in those brackets, you need to let Excel add them.
modytrane.

[modytrane]

Change the formula to:

Please Login or Register  to view this content.

and enter with Ctrl+Shft+Ent.

modytrane

[modytrane]

Sorry about that. Here's the correct formula.

Please Login or Register  to view this content.

Enter with Ctrl+Shft+Ent.

modytrane.

[darkyam]

How about =IF(AND(Countif(G31:G34,E19),Countif(H31:H34,B22),Countif(D31:D34,B3)),Max(Q:Q),"") That doesn't have to be confirmed with CSE.

[WorkMan]

I'm still having issues getting the correct number. I'm only receiving the highest number in the Q column, it's not qualifying it by the if statements...

Hmm.

[DonkeyOte]

It's not clear to me at least if you're looking to retrieve the Max associated Q value for all rows where prior conditions hold true... ie:

Please Login or Register  to view this content.

or if in fact you want MAX of all Q values if the above all hold true at least once (not nec. in same row) which is what darkyam has offered you...

[WorkMan]

Perfect! That worked, thanks for the help!