Adding year to quarterly equation to count

AshleeCREP

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)

Paul Newcome

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)

AshleeCREP

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

AshleeCREP

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

Paul Newcome

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