Reference sheet CountIf Contains

jpaulk

Hello,

I am trying to build a reference sheet that gives a summary of quarterly results. I need to count the row if the value contains either Exception or Samples. I tried many different versions only getting many different error messages. This is my last attempt getting a Invalid Operation.

=COUNTIF({2023 Q4 Monitoring}, CONTAINS(OR("Exception", "Samples")))

Any help is greatly appreciated.

bisaacs

Hey @jpaulk,

Could you create a helper column in the source sheet that counts the row if it contains either of those items, then use the helper column to use the COUNTIF function?

=IF(OR(CONTAINS("Exception", [Source Row]@row), CONTAINS("Samples", [Source Row]@row)), 1, 0)

=COUNTIF({2023 Q4 Monitoring}, 1)

Hope this helps!

bisaacs

Hey @jpaulk,

Could you create a helper column in the source sheet that counts the row if it contains either of those items, then use the helper column to use the COUNTIF function?

=IF(OR(CONTAINS("Exception", [Source Row]@row), CONTAINS("Samples", [Source Row]@row)), 1, 0)

=COUNTIF({2023 Q4 Monitoring}, 1)

Hope this helps!

jpaulk

Thank you @bisaacs, at that point, should I just do a report from the sheet? My end goal is to have data I can chart on a dashboard. additionally to show a trend since there is a separate sheet for each quarter.

bisaacs

Hey @jpaulk,

I think a Report would be another great option! You can filter by what certain columns contain, so that could be useful to you in this situation.

jpaulk

Thank you!