Formula Suggestion Needed

[dairenshaw]

Schedule.xlsx See attached the document needed for reference.

I'm building a campaign scheduling document to ensure that marketing campaigns aren't overlapping too much. To each campaign I add a drop date and criteria by section, for example campaign A will be on 24th April 2014 and targets customers in Asia, Active and Education.

I can only run 2 campaigns with matching criteria from each section in a 30 day period. Each campaign is to be checked for a clash over the past 30 days.

For example, campaign G is a clash as E/F are within 30 days backwards of Gs drop date and they match on atleast one in each section (Asia for region, active for status and finance for industry). In a separate document I've used named ranges to highlight 30 days backward and sum product to try to identify matching criteria but can't get them to work with the logic needed.


I've managed to achieve approximate results with extensive formulas and manual input but would like to automate the process.
Any suggestions or just pointing in the right direction would be greatly appreciated!

[nathansav]

Something like

=IF(AND(COUNTIF($C$6:$C6,">=" & C7-30)>0,SUM($H$6:$H6)>2),"Conflict","")

in rows 7 down.