How to use COUNTIF with OR and FIND functions

Options

I have a sheet where I want to find out how many rows contain certain strings of text. For example I have a formula set up where I am searching for a venue in a certain Local Authority that Contains "Park" in the venue Type and I am using the below formula;

=COUNTIFS([Local Authority]:[Local Authority], [Enter LA Name for LA Dash]#, [Registration Organisation Type]:[Registration Organisation Type], FIND("Park", @cell) > 0)

This works fine.

However I want to search for multiple venue types in the "Registration Organisation Type" Column - how would I return results for rows that contain "Park" and "Club" and "CITC" in Registration Organisation Type please? I'm stuck!

Many Thanks,

Best Answer

  • Alejandro Ortega
    Alejandro Ortega ✭✭✭
    Answer ✓
    Options

    Hey Stuart, i believe the only way of doing this on smartsheet is by adding multiple countifs with the different criteria, i've never been succesful using "OR" inside a countifs on smartsheet. You should get the results you want if you use the below formula:

    =COUNTIFS([Local Authority]:[Local Authority], [Enter LA Name for LA Dash]#, [Registration Organisation Type]:[Registration Organisation Type], "Park") + COUNTIFS([Local Authority]:[Local Authority], [Enter LA Name for LA Dash]#, [Registration Organisation Type]:[Registration Organisation Type], "CITC") + COUNTIFS([Local Authority]:[Local Authority], [Enter LA Name for LA Dash]#, [Registration Organisation Type]:[Registration Organisation Type], "Club")



    Please let me know if this worked out for you.

Answers

  • Alejandro Ortega
    Alejandro Ortega ✭✭✭
    Answer ✓
    Options

    Hey Stuart, i believe the only way of doing this on smartsheet is by adding multiple countifs with the different criteria, i've never been succesful using "OR" inside a countifs on smartsheet. You should get the results you want if you use the below formula:

    =COUNTIFS([Local Authority]:[Local Authority], [Enter LA Name for LA Dash]#, [Registration Organisation Type]:[Registration Organisation Type], "Park") + COUNTIFS([Local Authority]:[Local Authority], [Enter LA Name for LA Dash]#, [Registration Organisation Type]:[Registration Organisation Type], "CITC") + COUNTIFS([Local Authority]:[Local Authority], [Enter LA Name for LA Dash]#, [Registration Organisation Type]:[Registration Organisation Type], "Club")



    Please let me know if this worked out for you.

  • Stuart Maidment
    Options

    @Alejandro Ortega - Amazing, this works, and also worked with the use of the FIND function too to reduce the length of the formula a little.

    Amazing help and speedy reply, many thanks indeed.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!