how to calculate the number of projects who have an end date within 14 days?

Tabitha Thieman

I want to know how to calculate the number of projects who have an end date within 14 days but are in a specific portfolio status?

Joe Goetschel

If it was me, I would just do another status (Same one different staus) in the sheet summery, then make a 3rd and just use a Sum to combine the 2. This way you have the best of both worlds and you can then use it on a chart if needed.

Active projects = =COUNTIFS([Portfolio Status]:[Portfolio Status], "Active", [End Date]:[End Date], <=TODAY(14))

Active 2 Projects (Change "Active 2" to another status you want to us.

=COUNTIFS([Portfolio Status]:[Portfolio Status], "Active 2", [End Date]:[End Date], <=TODAY(14))

2 status summed together. see the photo above.

Does this help? I like this because then I can use the totals as a metric on a dashboard and the other 2 in a Chart to see where my total is coming from.

Joe Goetschel

Are these all on the same sheet or are they separately built as project plans and live in the same workspace?

Tabitha Thieman

@Joe Goetschel

Yes they are on the same sheet...

Tabitha Thieman

They are on one portfolio. Forgot to add that in there.

Thank you for you help.

Joe Goetschel

See if this help, I would just put this in Sheet Summery personally

You can modify the status to whatever you would like.

=COUNTIFS([Portfolio Status]:[Portfolio Status], "Active", [End Date]:[End Date], <=TODAY(14))

Tabitha Thieman

Ok, that works if I need it to pull one status in, but I need two status counts.

Joe Goetschel

If it was me, I would just do another status (Same one different staus) in the sheet summery, then make a 3rd and just use a Sum to combine the 2. This way you have the best of both worlds and you can then use it on a chart if needed.

Active projects = =COUNTIFS([Portfolio Status]:[Portfolio Status], "Active", [End Date]:[End Date], <=TODAY(14))

Active 2 Projects (Change "Active 2" to another status you want to us.

=COUNTIFS([Portfolio Status]:[Portfolio Status], "Active 2", [End Date]:[End Date], <=TODAY(14))

2 status summed together. see the photo above.

Does this help? I like this because then I can use the totals as a metric on a dashboard and the other 2 in a Chart to see where my total is coming from.

Tabitha Thieman

@Joe Goetschel Thank you so much!! This was very helpful.