Count IFS & Contains

raymond.j.riosiii94806

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

=Chris Palmer

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)