Trying to find specific headcount for each month of arrivals and how to include End Date

Hello All,

I have a formula currently counting the arrivals for a headcount but it is not counting who is staying past that month. How can I change my formula to accomodate not only new arrivals but also the current personnel that are there for future months. I.E. New arrivals + Current personnel

=COUNTIFS({TA Management Submissions (Overall) Range 5}, [Primary Column]$2, {TA Management Submissions (Overall) Range 3}, HAS(MONTH(@cell ), [Column2]@row))

Range 5: Type of Personnel

Range 3: Start Date

Need to incorporate End Date to finalize total headcount

Find more posts tagged with

Formulas

Accepted answers

All comments

Matt Lynn ACT

@Austin Probst Try this:

Note: Replace the [Start Date]:[Start Date] with your reference and you'll have to create a new reference for the end date column. Also I highly recommend naming your references so they're easier to understand and use fewer characters.

=COUNTIFS([Start Date]:[Start Date], IFERROR(MONTH(@cell ), 0) >= [Column2]@row, [End Date]:[End Date], <=TODAY())

Also: Assuming you don't go into this sheet every day you'll need to add an automation to this sheet to keep the today() function up to date. Let me know if you need help with that.

Paul Newcome

Try this:

=COUNTIFS({Personnel Range}, @cell = [Primary Column]$2, {Start Date Range}, @cell <= DATE(2024 + IF([Column2]@row = 12, 1, 0), IF([Column2]@row = 12, 1, [Column2]@row + 1), 1) - 1, {End Date Range}, @cell >= DATE(2024, [Column2]@row, 1))

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.