Count if text in cell contains specific item

I'm trying to count how many times a specific text string appears in a cell but tie it to an event name that appears multiple times. I've tried countifs with a contain. I tried reversing it and starting with an IF(Contains) and can't get anything to return what I need. In plain English, count If Trip Name = 24-03 AND Type contains B. I'm doing the function in another sheet so its a reference to this sheet. I run into errors with every formula I've come up with. Thanks


Answers

  • juliesilverio
    juliesilverio ✭✭✭✭
    edited 01/24/24

    Hi @jmhudso3,

    Here is the formula I suggest:

    =COUNTIFS({Trip Name Range}, "24-03", {Type Range}, CONTAINS("B", @cell))

    For the snapshot provided, the count is 7. You can adjust the trip name and type with the text in quotes to run this formula for your different criteria.

    Hope that helps!

    Julie Silverio, PMP

    Senior Manager, Program Leadership

    Xencor Inc.

    https://xencor.com/

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!