Formula Syntax for AGGREGATE and Multiple IF Statements

[fearonc]

I am trying to craft a formula for cell [E4] that I can drag down and generate dates for the data that fits the Column B “Venue” criteria and the Column C “Total” criteria >0. Formula results express the previous date from the target date where a total value >0 occurred for a specific venue (with the exception of the first date seen. Seems to behave when “Moose Pond” [$G$1] is loaded into the formula but reference to ERNC [$J$1] comes up FALSE. If I reverse the sequence position of $G$1 and $J$1 in the formula, the dates are correct for ERNC but now FALSE for Moose Pond. I think I am close but I have tried so many variations with left/right parentheses, quotation marks, etc. that my eyes have crossed. I am sure there is a way to do this. Formulas in columns G-I and J-L are dedicated to Moose Pond and ERNC respectively and seem to work. Also, I am not sure if there is a way to have the date result show up in the same row as the dates in Column A, but that is a minor distraction. Many thanks!

[Glenn Kennedy]

??? In E2, copied down:

=IF(AND(OR(B2="Moose Pond",B2="ERNC"),C2>0),A2,"")

and in F2, copied down:
=IF(E2<>"",LEFT(B2),"")

See sheet.

????

[fearonc]

Well, that was AWESOME! Sure cleaned up a formula that was way out of my league. Thanks so much!

[Glenn Kennedy]

Your own formulae looked pretty awesome, too... but maybe they went around in cirlces a bit!!

Anyhow, you're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.