Dashboard formula counting rows within date range

bthompson@fa

First post, Longtime SSheet user but new to formulas and stuck on this last one for a dashboard. 

I am trying to count rows where the due date is within a range, the form created date is within 14 days of the due date and the status is not "complete." I have tried a wide variety of solutions. 

=COUNTIFS({FAMS Marketing Calendar | 2019 Range 6}, @cell >= TODAY(-15), {FAMS Marketing Calendar | 2019 Range 6}, @cell < TODAY(15), {FAMS Marketing Calendar | 2019 Range 2}, @cell <> "Completed", {FAMS Marketing Calendar | 2019 Range 4}, <{FAMS Marketing Calendar | 2019 Range 1})

Rang 4 is the created date and I have tried removing the time from this also.

I think I am close and any help would be appreciated.

 

Paul Newcome

Using Range 1 as the criteria is where you will want to make the change. What is in Range 1?

bthompson@fa

Such a newbie I just realized you can name reference. Screen shots attached also. 

Range 1 is the row creation date = date + Time, I also tried the formula by removing time out of the creation date with what I called request date. 

 

Paul Newcome

Is range 4 or range 1 the created date?