Dashboard formula counting rows within date range

07/12/19 Edited 12/09/19

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.

 

Comments

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

    thinkspi.com

  • 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. 

     

    Screen Shot 2019-07-12 at 5.12.27 PM.png

    Screen Shot 2019-07-12 at 5.12.37 PM.png

    Screen Shot 2019-07-12 at 5.13.03 PM.png

    Screen Shot 2019-07-12 at 5.13.45 PM.png

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Is range 4 or range 1 the created date?

    thinkspi.com

Sign In or Register to comment.