Need help with a cross-sheet reference formula.

Jason_AZ86Biz

I am trying to create a cross-sheet formula so I can count the number of projects for a specific PM where the show start data is in the month of January and year is 2024. The source sheet contains show start dates that are in 2025 so I want to be able to avoid counting them by mistake. I'm basically trying to count the number of projects for each month for each project manager on a summary sheet. It is stumping Smartsheet's AI so I'm hoping a human pro can help me out. Thank you, community!

Hollie Green

=countifs({show start date},>DATE(2023, 12, 31),{Show start date},<DATE(2024, 2, 1),{PM},[PM]@row)

You will need to create your references on the source sheet and then alter the formula for each month/year column with the appropriate dates for greater than or less than.

che.rabajante

Hello @Jason_AZ86Biz

Could you share a screenshot of what you're trying to accomplish, using dummy data if needed? It will help me find the right solution.

Thank you!🙂

che

Jason_AZ86Biz

Hi Che,

Here is the source sheet:

Here is the target sheet:

I want to be able to count the projects in a given month/year by PM from the source sheet and display them on the target sheet. Some projects have start dates in 2024 and 2025.

Hollie Green

=countifs({show start date},>DATE(2023, 12, 31),{Show start date},<DATE(2024, 2, 1),{PM},[PM]@row)

You will need to create your references on the source sheet and then alter the formula for each month/year column with the appropriate dates for greater than or less than.

Jason_AZ86Biz

This worked great! Thank you @Hollie Green !