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
Answers
-
@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.
-
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))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!