Return the count project completed ontime and late per month

I couldn't figure out the formula for COUNITFS, with referencing, which can do the following task.

  1. In Metrics Sheet, add the count of completed projects in Sheet1 on-time or late per month and year listed.
  2. The problem I am having is, I could Reference ranges for Planned End date and Actual End date, and look for specific dates and count the total completed project with the formula -
    1. =COUNTIFS({Project Status List}, "Complete", {Actual End Date List}, NOT(@cell = ""), {Actual End Date List}, MONTH(@cell) = [M-1]$2, {Actual End Date List}, YEAR(@cell) = [M-1]$3). But I couldn't compare {Actual End Date} element in the range with corresponding {Planned End Date} element. Conditions for - On-Time Project End Date <= Planned End Date , Late Projects Projects End Date > Planned End Date for each month. I appreciate any help. thank you.


Answers

  • Purnima Gore_PGPS
    Purnima Gore_PGPS ✭✭✭✭✭

    Hi @Surya

    Could you add the data you are looking for in the Metrics sheet below so I can see what you are trying to achieve please?

    My first thought is that you could use the summary tab to get the information, use a helper column to pull that information in and then reference the figures into the Metric Sheet.

    If you can provides some numbers that means I can look at the formula and see why you are not getting the numbers you need.

    Thanks

    Purnima

  • Surya
    Surya ✭✭✭
    edited 01/23/24

    Hi @Purnima Gore_PGPS , thanks for looking into this, I need to get the following data based on Sheet1 in Metrics Sheet -

    I prefer to have a formula than summary sheet as I need to build multiple metrics and graphs


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!