COUNTIFS Help

Carlos Yanes

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.

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!!