COUNTIFS Help

I'm trying to use this formula, but am getting an error and I'm not sure what I'm doing wrong.

=COUNTIFS({ElevationEscalation RCA Triage::[Triage Created]}, ">=" &TODAY(),{Elevation Escalation RCA Triage::[Triage Created]}, "<="& TODAY() + 5,{Elevation Escalation RCA Triage::[Intake Source]},"Intake/Elevation - Low")

I'm trying to write a formula where it counts how many rows are between 0-5 days old, based on a date column called Triage Created on a sheet called Elevation Escalation RCA Triage and where the intake source column is equal to Intake/Elevation - Low

I will also then follow the same process for 6-10 days and 11+ days.

Find more posts tagged with

Accepted answers

All comments

rrenee

Hi Carlos,

When using the TODAY() formula, a neat trick is to put the + or - number of days right in the parenthesis. Based on what you described, I wrote a formula to count triage dates created five days ago or greater, including today.

=COUNTIFS({ElevationEscalation RCA Triage::[Triage Created]}, @cell >= TODAY(-5), {ElevationEscalation RCA Triage::[Triage Created]}, @cell <= TODAY(0), {Elevation Escalation RCA Triage::[Intake Source]},"Intake/Elevation - Low")

Similarly, I adjusted the TODAY(#) numbers for 6-10 days as so:

=COUNTIFS({ElevationEscalation RCA Triage::[Triage Created]}, @cell >= TODAY(-10), {ElevationEscalation RCA Triage::[Triage Created]}, @cell <= TODAY(-6), {Elevation Escalation RCA Triage::[Intake Source]},"Intake/Elevation - Low")

P.S. the "@cell" and 0 in the today() are not necessary to include, but it can make reading the formula easier.

I hope this helps!

Renée

Carlos Yanes

HI @rrenee,

Thank you for your help. I am still getting an unparseable error when using the formula and pointing at the respective columns. This is what I'm using:

=COUNTIFS({Elevation Escalation RCA Triage Range 4}, ">=" & TODAY(),{Elevation RCA Triage Range 4}, "<=" & TODAY() +5,{Elevation Escalation RCA Triage Range 3}, "Intake/Elevation - Low")

Can you help me correct what I am doing wrong?

Carlos

rrenee

Hi @Carlos Yanes,

I don't believe(?) you need to be using quotation marks or the "&" symbol around the greater than or equal to sign. I recognize that format from Excel, but Smartsheet is a bit different in how you format the formulas.

Hence, I think it looks good, just make your today()s as >= TODAY() instead of ">="& TODAY() like so:

=COUNTIFS({Elevation Escalation RCA Triage Range 4}, >= TODAY(), {Elevation RCA Triage Range 4}, <= TODAY(5),{Elevation Escalation RCA Triage Range 3}, "Intake/Elevation - Low")

For the actual logic, your formula searches for dates in the next five days. I read your initial question as looking for dates in the past 5 days, so I rewrote it for that below in case that is still needed:

=COUNTIFS({Elevation Escalation RCA Triage Range 4}, <= TODAY(), {Elevation RCA Triage Range 4}, >= TODAY(-5),{Elevation Escalation RCA Triage Range 3}, "Intake/Elevation - Low")

Let me know if it you still have any unparseable errors!

Carlos Yanes

Thank you!!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.