Max Date if one of a few conditions applies

I'm trying to find the latest date from a column if certain conditions are met. I have this formula to find the Max date when looking for an account number:
=MAX(IF('Sheet2'!$Q:$Q=B1,'Sheet2'!$J:$J))
Here, B1 is the account #, Q is the range searched, and J is the date. Q & J are on a different sheet (Sheet2).

What I need to do is get the Max date for that account # if the corresponding value in Column G of sheet2 is greater than 0 and also if the corresponding 1st 2 characters in Column L of sheet2 begins with one of the following:
CK
CA
AJ
CP

Is this possible? It's a bit beyond me. Thanks ahead of time for any help!

Oh wow. I'm seriously impressed. You, sir, are a flipping genius!! Thank you!!

First, thank you both for the help. Second, Excel seems to freeze up whenever I use one of these formulas. Of course, there is a lot of data. Sheet 1 can have as many as a couple hundred rows and sheet2 has around 115k rows. Things seem fine until one of the formula is used though. By freeze, I mean that the workbook becomes unresponsive. I don't get the spinning wheel of death and if you open the Force Quit options it does not show Excel as not responding (I'm on a Retina Macbook Pro).

Any ideas what's happening or how I can work around this?

Unfortunately, I think that's beyond me. I've never done VBA and am not even sure I understand what it is. By taking as much time, do you mean it takes as much time to setup (i.e. type in) or that the calculations it performs like as long? If the calculations are quicker, that might help.

Hmm, would it help if I put the excel file on an external USB 3 SSD that's not nearly as full as my boot drive? If all conditions are equal, the external USB 3 SSD transfer speed is about the same as the boot drive. So here, it might be faster b/c it's not nearly as full.

Also, is there a way to get Excel to use more RAM?

Oh thanks, good advice. When I'm not on my day job, I'm an indie filmmaker so I use the drive for video editing. It pretty much maxes out USB 3 speed and is a big hit in that community. I'll give it a try. Can't hurt.

Oh thanks, good advice. When I'm not on my day job, I'm an indie filmmaker so I use the drive for video editing. It pretty much maxes out USB 3 speed and is a big hit in that community. I'll give it a try. Can't hurt.

Ok, I think it works now. I did a little workaround by making tables of the data on the sheet that requires the array formula so it would calculate everything w/o the need to drag & copy. Then I added the array last so nothing else needed to be done. Looks like it works, yea!!

As a side note, the reason I'm going to all this trouble is b/c the company that makes & supports our banking software calculates the last payment date for an account as the last date something was done to it. Could be that was when a charge was added to it, which is NOT the same as a payment. But, if you go by there last date then you could get sued in a hurry. To me, this is catastrophic lazy calculation #5 or 6 on their part. I keep wondering how no one else noticed these things. I keep not getting answers.

Again, thanks for all the help!!