This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hi,
I need to count the number of tasks that are the same as the milestone and the number of completed tasks from the input table, and create a pivot table where the status and progress of each task can be tracked
Additionally, I require a conditional formula in the Milestone Complete Task (input table) to calculate the number of completed tasks based on the Status column in the input table.
Result : Need automation in pivot table :
| Result | ||||
|---|---|---|---|---|
| Milestone | Task Target | Task Completed | Target Date | TAT Stats |
| Milestone#1 | 4 | 4 | 3/10/2023 | On-Time |
| Milestone#2 | 8 | 5 | 4/4/2023 | Delay |
| Milestone#3 | 1 | 0 | 4/10/2023 | Not Pick |
| Milestone#4 | 4 | 2 | 4/27/2023 | Not Pick |
| Milestone#5 | 7 | 0 | 5/15/2023 | Not Pick |
Input Table :
| S.No | Task | Milestone | Milestone Count# | Ownership | Start Date | End Date | Completion % | Milestone Complete Task | Status | Actual Complete date | TAT | TAT Stats |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Task1 | Milestone#1 | 4 | Sunil Kumar | 3/1/2023 | 3/10/2023 | 100% | Completed | 3/10/2023 | 0 | On-Time | |
| 2 | Task2 | Milestone#1 | 4 | Sunil Kumar | 3/1/2023 | 3/10/2023 | 100% | Yet to Pick | 3/10/2023 | 0 | On-Time | |
| 3 | Task3 | Milestone#1 | 4 | Het Kothari | 3/1/2023 | 3/10/2023 | 100% | Completed | 3/10/2023 | 0 | On-Time | |
| 4 | Task4 | Milestone#1 | 4 | Het Kothari | 3/1/2023 | 3/10/2023 | 100% | Completed | 3/10/2023 | 0 | On-Time | |
| 5 | PIM Master Sheet | 0 | 0 | On-Time | ||||||||
| 5.1 | Task1 | Milestone#2 | 8 | Het Kothari | 3/15/2023 | 4/4/2023 | 100% | Completed | 5/4/2023 | 30 | Delay | |
| 5.2 | Task2 | Milestone#2 | 8 | Het Kothari | 3/15/2023 | 4/4/2023 | 100% | On Hold | 5/4/2023 | 30 | Delay | |
| 5.3 | Task3 | Milestone#2 | 8 | Het Kothari | 3/15/2023 | 4/4/2023 | 100% | Completed | 5/4/2023 | 30 | Delay | |
| 5.4 | Task4 | Milestone#2 | 8 | Het Kothari | 3/15/2023 | 4/4/2023 | On Hold | 5/4/2023 | 30 | Delay | ||
| 5.5 | Task5 | Milestone#2 | 8 | Het Kothari | 3/15/2023 | 4/4/2023 | Completed | 5/4/2023 | 30 | Delay | ||
| 5.5 | Task6 | Milestone#2 | 8 | Het Kothari | 3/15/2023 | 4/4/2023 | 100% | Completed | 5/4/2023 | 30 | Delay | |
| 5.6 | Task7 | Milestone#2 | 8 | Het Kothari | 3/15/2023 | 4/4/2023 | Completed | 5/4/2023 | 30 | Delay | ||
| 5.7 | Task8 | Milestone#2 | 8 | Sunil Kumar | 3/15/2023 | 4/4/2023 | 30% | On Hold | 5/4/2023 | 30 | Delay | |
| 6 | Admin Controller | 0 | 0 | On-Time | ||||||||
| 6.1 | Task1 | Milestone#3 | 1 | Het Kothari | 4/6/2023 | 4/10/2023 | Yet to Pick | -45026 | Not Pick | |||
| 7 | Attribute Upload | 0 | 0 | On-Time | ||||||||
| 7.1 | Task1 | Milestone#4 | 4 | Het Kothari | 4/10/2023 | 4/27/2023 | Completed | -45043 | Not Pick | |||
| 7.2 | Task2 | Milestone#4 | 4 | Het Kothari | 4/10/2023 | 4/27/2023 | Completed | -45043 | Not Pick | |||
| 7.3 | Task3 | Milestone#4 | 4 | Het Kothari | 4/10/2023 | 4/27/2023 | In Progress | -45043 | Not Pick | |||
| 7.4 | Task4 | Milestone#4 | 4 | Sunil Kumar | 4/10/2023 | 4/27/2023 | Yet to Pick | -45043 | Not Pick | |||
| 8 | Adapter | 0 | 0 | On-Time | ||||||||
| 8.1 | Task1 | Milestone#5 | 7 | Het Kothari | 4/27/2023 | 5/15/2023 | 60% | In Progress | -45061 | Not Pick | ||
| 8.2 | Task2 | Milestone#5 | 7 | Het Kothari | 4/27/2023 | 5/15/2023 | In Progress | -45061 | Not Pick | |||
| 8.3 | Task3 | Milestone#5 | 7 | Het Kothari | 4/27/2023 | 5/15/2023 | Yet to Pick | -45061 | Not Pick | |||
| 8.4 | Task4 | Milestone#5 | 7 | Het Kothari | 4/27/2023 | 5/15/2023 | Yet to Pick | -45061 | Not Pick | |||
| 8.5 | Task5 | Milestone#5 | 7 | Het Kothari | 4/27/2023 | 5/15/2023 | Yet to Pick | -45061 | Not Pick | |||
| 8.6 | Task6 | Milestone#5 | 7 | Het Kothari | 4/27/2023 | 5/15/2023 | Yet to Pick | -45061 | Not Pick | |||
| 8.7 | Task7 | Milestone#5 | 7 | Het Kothari | 4/27/2023 | 5/15/2023 | Yet to Pick | -45061 | Not Pick |
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
It is helpful for me, Thanks
Glad this helped. Feel free to mark as solution to help those who Search
Thanks
It is helpful for me, Thanks
Hi sunil kumar6
#1 On a Mac you cannot get what you expect with a PivotTable as there's no way, in the "classic" PivotTable to aggregate non-numeric values (ie. your TAT Stats). This is doable with a Power PivotTable but the latter isn't available on Mac
#2 If the # of Task Completed is based on column Status then - with your Inputs - the count of Completed tasks for Milestone #1 is 3 and not 4: