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
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!