# Countifs in date range and value range.

Options

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.

• ✭✭✭✭✭✭
Options

@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

• Options

@Melissa Boehl I'm afraid I cannot share workspaces or sheets outside of my organization.

• ✭✭✭✭✭✭
edited 08/12/22
Options

@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

• Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!