Construction Phase Count Formula

I'm trying to count how many projects are in the construction phase each month using this formula:

=COUNTIFS({Master Project List Range 1}, "2 - Construction", {Master Project List Range 2}, <=Month@row, {Master Project List Range 3}, >=Month@row)

It works, but I want to make sure it's also counting projects that are starting after the first of the month. For example, it counts 24 project in the construction phase for May, but when I print out master gantt and count the projects manually, I'm getting 30 because there are 6 projects starting in the middle of May. How do I adjust the formula to account for all projects already in the construction phase and those entering the construction phase in any given month?

Find more posts tagged with

Formulas

Project Management

Accepted answers

Paul Newcome

Ok. So if "Month@row" contains an actual date, I would use something like this:

=COUNTIFS({Master Project List Range 1}, "2 - Construction", {Master Project List START DATE}, VALUE(IFERROR(YEAR(@cell), 9999) + RIGHT("0" + IFERROR(MONTH(@cell), 99), 2)) <= VALUE(YEAR(Month@row) + RIGHT("0" + MONTH(Month@row), 2)), {Master Project List END DATE}, VALUE(IFERROR(YEAR(@cell), 0) + RIGHT("0" + IFERROR(MONTH(@cell), 0), 2)) >= VALUE(YEAR(Month@row) + RIGHT("0" + MONTH(Month@row), 2)))

All comments

Paul Newcome

What data exactly do you have in "Month@row"? Is it just a number? Do you have anything to factor in the year so that something in April of 2025 is not counted for April of 2026?

Hillary

I have a helper sheet that has a date column.

Hillary

Paul Newcome

Ok. So if "Month@row" contains an actual date, I would use something like this:

Hillary

Thank you very much, Paul! It worked perfectly.

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.