Return the count project completed ontime and late per month

Surya

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.

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

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