Countifs in date range and value range.
I am stuck on this formula, it doesn't become unparseable but it doesn't result in an accurate count. My goal with this is to is to keep an annual tally of a specific age range of patient the formula is as follows: =COUNTIFS({Trauma Chart Audit Age}, <15) + COUNTIFS({Trauma Chart Audit Age}, NOT(ISNUMBER(@cell)), {Trauma Chart Audit Date}, AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 12, 31))) - 10.
The NOT(ISNUMBER(@cell)) is to account for ages under one year.
Answers
-
@Steven Canepa Would you mind making a copy of the sheet or sheets and clear the data and share with me? This would be the easiest way for me to identify the issue. mboehl@withersravenel.com
Melissa Boehl
Smartsheet Architect | TurningPoint Energy
-
@Melissa Boehl I'm afraid I cannot share workspaces or sheets outside of my organization.
-
@Steven Canepa okay, I understand. Based on what I can see from the formula you shared, the problem is with the 2nd countifs statement. There is no need use "and" within a countifs statement because the "and" is already implied. I rewrote your formula below. The placement of the -10 is based on subtracting it from the total of both statements and can be placed inside the parentheses if it only applies to the second countif. Please let me know if this corrects the count.
=(COUNTIFS({Trauma Chart Audit Age}, <15) + COUNTIFS({Trauma Chart Audit Age}, NOT(ISNUMBER(@cell)), {Trauma Chart Audit Date}, >= DATE(2022, 1, 1),{Trauma Chart Audit Date},<= DATE(2022, 12, 31))) - 10.
Melissa Boehl
Smartsheet Architect | TurningPoint Energy
-
The formula is coming up with the same result, I can't figure out what it's counting that is inflating the number of rows.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 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!