Adding an extra If function in to Array Formula

[andrewsco]

Hi everyone,

I have a formula in a finance transaction document that uses the following formula to return the latest date in a list that a certain stock was purchased:

=iferror(ArrayFormula(if(AE3="","",(MAX(IF(J$3:J$1001=AE3,A$3:A$1001))))),"")

AE = Stock ticker
J = Stock Ticker
A = a unique date for each transaction

What I would like to do is add to this formula that returns the same type of thing but ONLY if the transaction Account (Column C) = "RESP"

I'm sure there is a simple way to modify the formula above but i've tried for hours and can't seem to figure it out.

Hoping someone might be able to help based on the above description. This is in google sheets by the way as opposed to Excel but imagine the formula will be the same.

Thanks in advance
Andrew

 

[Fluff]

This is in google sheets

In that case you should post you question in the General Discussion & Other Applications section of the board. I have moved it for you this time.

 

[Fluff]

Assuming Sheets works the same way as Excel with array formulae try

=iferror(ArrayFormula(if(AE3="","",(MAX(IF((J$3:J$1001=AE3)*(C$3:C$1001="Resp"),A$3:A$1001))))),"")

 

[andrewsco]

Thanks so much!

And noted about the Google Sheets forum - will make sure to do that next time.

Thanks

 

[Fluff]

You're welcome & thanks for the feedback.