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

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?

• ✭✭✭✭✭✭

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?

• Joe Goetschel
• @Joe Goetschel

Yes they are on the same sheet...

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

Thank you for you help.

• ✭✭✭✭✭✭

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))

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

• ✭✭✭✭✭✭

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
• @Joe Goetschel Thank you so much!! This was very helpful.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!