Reference sheet CountIf Contains

Options
jpaulk
jpaulk ✭✭✭
edited 04/25/24 in Formulas and Functions

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.

Best Answer

  • bisaacs
    bisaacs ✭✭✭✭✭
    Answer ✓
    Options

    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!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭
    Answer ✓
    Options

    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!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • jpaulk
    jpaulk ✭✭✭
    Options

    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
    bisaacs ✭✭✭✭✭
    Options

    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.

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • jpaulk
    jpaulk ✭✭✭
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!