# Adding year to quarterly equation to count

Options

I want to count how many projects in each network have expected completion dates within the quarter per year. For example, in Upstream, I know 3 projects will complete in Q1 2024 but 1 will complete in Q1 2025. The formula I'm using won't account for the year and count correctly for what I know are the actual expected launches. Ask is for a formula that I can apply to count how many project completions there are per network, per quarter, per year while referencing another sheet's data.

Formula I have: =COUNTIFS({Expected Completion Date}, <=[Q1 dates]2, {Business Unit}, \$Network@row)

• ✭✭✭✭✭✭
Options

You would add another range criteria set where the date column is the range and the criteria is the YEAR is the year in the date cell.

{Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(Q1 Dates]2)

• Options

Hi Paul

Sincere thanks for your reply! Unfortunately I don't follow....are you saying the formula would need to be updated to this below? If so, it says unparseable. Bolded part is what I added from your feedback.

=COUNTIFS({Expected Completion Date}, <=[Q1 dates]2, {Business Unit}, \$Network@row, [Q1 dates]5:[Q1 dates]6, IFERROR(YEAR(@cell), 0) = YEAR(Q1 Dates]2))

• Options

@Paul Newcome (I meant to tag you in my reply above)

• ✭✭✭✭✭✭
Options

The {Date Range} would be whatever cross sheet reference you are using to evaluate the dates in the COUNTIFS.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!