Combination Sum / Vlookup / Min / If Formulas

Hi,

I've searched the forums and I'm a relatively advanced user in my own way but I am being stumped by an issue.

I have a list of film products for four separate territories, each territory with a a spreadsheet. There are release dates & sales against each title BUT the titles do not always exist in each sheet. Sometimes a title will exist in a spreadsheet but with a text value in both the release date & sales cell "NR" etc. I can incorporate IF & SUMIF functions for the sales - if the title has NR in each sheet it brings back NR, if any have sales then it adds them up etc. but I can not crack how to get the earliest release date. It's fine if the title exists in each sheet with a date against it but as soon as it is not in one it kills the VLOOKUP and if there as an NR instead of a date it kills the function as well.

I've attached a dummy sheet and essentially need a master list which brings back the earliest date for a title from each column B and sums the sales for a title from each column c.

Book2.xlsx

Thanks a lot in advance for any help!

Hi armprabhu - apologies for being daft but can't see an attachment. Thanks!

Thanks - i can see the formula you've added in but I can't make that work across multiple sheets. I.e. it's fine in one list but if you have the same title in 4 different lists - 3 of which have dates and one has "NR". I would need the formula to discount the one "NR" and bring the earliest of the other 3 dates back. There is no issue where they are all "NR" or all dates but it's the combination that breaks it for me.

Pl See the attached file.I have slightly changed formula and macro.
Macro pastes all data from each sheet (1 to 4) to "Consolidated" sheet.Then in that list min date without duplication is processed.
Pl see
GREEN ZONE has three different dates in each sheet & NR in sheet4.
Consolidated list gives the lowest date from Sheet1.
LAND OF THE LAST does not have any date.