Trying to create a Countif formula that includes the # of workers allocated based on date range
I'd like to create a formula that would count all of the entries in a specific column (# of Workers Allocated) IF TODAY is within the date range that corresponds to each job entry (highlighted). As I've been tinkering with the formula, I can't quite seem to get it to return what I need. It gave me an answer of 4 (second picture), and I can't figure out why?
Thoughts?
Thank you for any help considered!
Best Answer
-
Hello there! Have you considered using the summary instead of the body of the sheet. I have always found that when referring to a column of data, the summary works best. I am not sure what your True/False column is but I have a formula that works in the summary and can be adjusted if needed to add the sheet itself.
Also, the countif formula counts the rows that meat criteria. Is that what you are looking to do or are you looking for a total # of workers? For total workers, change to the Sumif.
See screenshots below. Hope this helps!
=COUNTIFS([Start Date]:[Start Date], <=TODAY(), [End Date]:[End Date], >=TODAY())
Melissa Boehl
Smartsheet Architect | TurningPoint Energy
Answers
-
Hello there! Have you considered using the summary instead of the body of the sheet. I have always found that when referring to a column of data, the summary works best. I am not sure what your True/False column is but I have a formula that works in the summary and can be adjusted if needed to add the sheet itself.
Also, the countif formula counts the rows that meat criteria. Is that what you are looking to do or are you looking for a total # of workers? For total workers, change to the Sumif.
See screenshots below. Hope this helps!
=COUNTIFS([Start Date]:[Start Date], <=TODAY(), [End Date]:[End Date], >=TODAY())
Melissa Boehl
Smartsheet Architect | TurningPoint Energy
-
It does! Thank you. The true/false column was to help trigger the Count, but your suggestion for the SUMIF changed everything! Thank you.
-
@zb14 Awesome! Please be sure to mark my answer as the answer to your question so other users can see how its resolved. Thank you!!
Melissa Boehl
Smartsheet Architect | TurningPoint Energy
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 405 Global Discussions
- 215 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!