Adding year to quarterly equation to count
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)
Answers
-
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)
-
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))
-
@Paul Newcome (I meant to tag you in my reply above)
-
The {Date Range} would be whatever cross sheet reference you are using to evaluate the dates in the COUNTIFS.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!