How to use COUNTIF with OR and FIND functions

Stuart Maidment

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,

Alejandro Ortega

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.

Alejandro Ortega

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

@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.