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.

Find more posts tagged with

Formulas

Support

Solution Center

functionality

Conditional Formatting

Accepted answers

All comments

Melissa Boehl

@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

Steven Canepa

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

Melissa Boehl

@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.

Steven Canepa

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.