Count IFS & Contains

Options

I'm trying to count the number of times a specific string of text is referenced in a column on another sheet. I've tried multiple ways, but it doesn't seem to come out right -

=COUNTIFS({KPHI: SHP Master Patient Tracker_ProcType}, "TAVR", {KPHI: SHP Master Patient Tracker_Comp@Proc}, CONTAINS("Arrhythmia, 1st Degree AV Block", @cell)) - Results in 0 (which is fine; however, I want the "TAVR" and "Arrhythmia, 1st Degree AV Block" to be referenced cells vs hard coded.

When I use, =COUNTIFS({KPHI: SHP Master Patient Tracker_ProcType}, =$IndexID$24, {KPHI: SHP Master Patient Tracker_Comp@Proc}, CONTAINS(=[Primary Column]@row, @cell)), I get the #INVALIDOPERATION error.

What am I doing wrong?

I'm trying to create a formula that counts if

  1. The reference sheet, Procedure Type = TAVR
  2. The reference sheet, Procedure Date = In the Past
  3. The reference sheet, Complications contains the @cell value

Please help

Tags:

Answers

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭
    Options

    Hello @raymond.j.riosiii94806

    I can spot a reason for the invalid operation error on the second formula:

    =COUNTIFS({KPHI: SHP Master Patient Tracker_ProcType}, =$IndexID$24, {KPHI: SHP Master Patient Tracker_Comp@Proc}, CONTAINS(=[Primary Column]@row, @cell))

    Try removing the additional "=" signs as they aren't needed.
    There should only be one "=" at the beginning, having more will result in errors.

    For the first formula:
    I haven't tested this out but it would be helpful to see a screen shot of the referenced sheet and the columns you're trying to work with.

    =COUNTIFS({KPHI: SHP Master Patient Tracker_ProcType}, "TAVR", {KPHI: SHP Master Patient Tracker_Comp@Proc}, CONTAINS([TAVR Complication Statistics]@row, @cell))

    If you're still having issues with this perhaps the "@cell" portion can reference the row instead.

    Perhaps removing the CONTAINS portion will reference the row directly without any hardcoding:

    =COUNTIFS({KPHI: SHP Master Patient Tracker_ProcType}, "TAVR", {KPHI: SHP Master Patient Tracker_Comp@Proc}, [TAVR Complication Statistics]@row)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!