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.
- In Metrics Sheet, add the count of completed projects in Sheet1 on-time or late per month and year listed.
- 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 -
- =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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!