Snapshot of Smartsheet comparing TODAY to Start and End Dates

I have a grid sheet full of contracts: each row is a contract. Each row has a START DATE and END DATE along with other details about the contract. I was tasked with creating a dashboard to represent this grid sheet over time.

I am now creating a reference sheet to pull "snap-shots" of data from the grid sheet. I put in a DATE column (the first of each month to pull monthly numbers). I am struggling with the formula for reference. Example: to count the # OF CONTRACTS active in a month I typed…

=COUNTIF({FY 24.25 NONUCR Range 1}, AND({FY 24.25 NONUCR Range 1} < Date@row, {FY 24.25 NONUCR Range 2} > Date@row))

*Range 1 is the START DATE and Range 2 is the END DATE from the grid sheet.

But this gave me an error and wouldn't work.

Any thoughts on how I can use the START and END DATES as reference for a monthly date to pull numbers from the original grid sheet?

Answers

  • Isis Taylor
    Isis Taylor ✭✭✭✭✭✭

    @BSReid24 You may need to try COUNTIFS vs COUNTIF as you have more than one criteria

    Isis Taylor

    🎓️ Core App and Project Management Certified 🏅

    🌟Peer Connect, Mobilizer, and Early Adopter Program

    Business Analyst Senior

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!