COUNTIF Question

Hello


We have a sheet that contains variations of words in a multi select column.

For example; Lisa, Lisa 1, Lisa 2.

I am trying to count how many times the word Lisa appears, regardless of the variation, i.e. Lisa 1 or Lisa 2.

I've tried lots of different formulas but nothing is working;

=COUNTM({Status Range 1}, ="Lisa", @cell)

=COUNTIF({Status Range 1}, CONTAINS("Lisa", @cell))

Can anyone help?

Many thanks

Lisa

Tags:

Best Answer

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭

    @Lisa B 2022

    the only way I've found to do this is to count the occurrences of each specific variation and add them together:

    =SUM(COUNTIFS({Status Range 1}, HAS(@cell, "Lisa")), COUNTIFS({Status Range 1}, HAS(@cell, "Lisa 1")), COUNTIFS({Status Range 1}, HAS(@cell, "Lisa 2")))

    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

  • @Jeff Davies , many thanks but unfortuntely it didnt work, getting back the #INCORRECT ARGUMENT SET error.

  • Leibel Shuchat
    Leibel Shuchat ✭✭✭✭✭

    @Lisa B 2022

    Assuming all your versions of Lisa Start with Lisa (and the difference is only ion what comes afterwards), you can do as following:

    1. In your source sheet Create a Multi-Select Column Called 'Lisa Removed'. Put in a Column Formula: =SUBSTITUTE([email protected], CHAR(10) + "Lisa", "")
    2. In a second column put in the following formula: =COUNTM([email protected]) - COUNTM([Lisa Removed]@row)

    The total sum of this column would give you the total qty of times the name Lisa shows up.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭
    Answer ✓

    @Lisa B 2022

    Check that all your parentheses, brackets, and commas are there. I copied that formula directly from a working cell in my test sheet and just replaced the ranges and values to match your example.

    Be sure the color-coding on the parentheses all match up.


    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

  • @Leibel Shuchat , many thanks but it's not working. In the first column, it's removing multiples of 'Lisa and leaving the remaining wording. The second formula returns #UNPARESEABLE

  • Leibel Shuchat
    Leibel Shuchat ✭✭✭✭✭

    @Lisa B 2022

    Did you make the first column a Multi Select?