MAX / MIN Lookup with Dates and Other Criteria

Hi all,

I'm trying to sort out a formula that allows me to essentially show the growth of an email list between two dates. My issue is that the data that I'm using has multiple email lists so I also need lookup against only the list I want (which is being chosen from a dropdown validation list). The formula I currently have is this:

Please Login or Register  to view this content.

This should:

1) Look for largest (most recent) date in column C of 'Data' that is greater than that in H6
2) Match the email list in F3 against column A in 'Data'
3) Return the list size (column 19 at the end of the INDEX formula)
4) Divide this by all of the above, except looking for the smallest (least recent) date in column C that is larger than H6.

Hope that all makes sense. Any help appreciated!

TIA,

FermentedR

Will you please attach a sample Excel workbook?

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Too vague?!

See attached example. Thanks!

Originally Posted by Fermented Reptile

Too vague?!

No, it's just easier to solve if one can see the layout and the formulae in place.

So, for anyone who might be interested, I've found a solution for how to do this. I've been unable to create a single mega-formula: the MAX function wasn't doing quite want I wanted. Instead I've created a couple of calculations in a Control worksheet and then have a formula on my main page referencing these.

On the Control sheet I've used the following formula to get the date of the latest iteration of the mailing list I want to reference:

Please Login or Register  to view this content.

For the earliest I simply replace MAX with MIN.

Thereafter a simple SUMPRODUCT formula on my main page does the rest:

Please Login or Register  to view this content.

Not the most elegant of solutions, but it works!

FermentedR