Count entries in a range that has text and number+text entries

Evan I

Im trying to count the number of times street avenue occurs in a column based on the range a user enters. I have tried using countifs >=starting range and less than ending range, but it only counts the number/text entries. Looking below if I did countifs({avenue},>=110, {avenue}<111) I only get 2 as the count, but I should get 5. How do I make this work.

Paul Newcome

You are going to need another helper column that strips the letter(s) off of the end and converts it to a number.

=VALUE(SUBSTITUTE([Nearest Avenue]@row, "A", ""))

Paul Newcome

Happy to help. 👍️

You can actually shorten that a little bit by removing the IFs and CONTAINSs and just nesting the SUBSTITUTEs. Saves you a bit of work if you have anything else to strip out because you can just add another SUBSTITUTE to the front (inside of the VALUE function of course) and then add what you need to remove to the end.

=VALUE(SUBSTITUTE(SUBSTITUTE([Nearest Avenue]@row, "A", ""), "B", ""))

Andrée Starå

https://community.smartsheet.com/discussion/109664/count-entries-in-a-range-that-has-text-and-number-text-entries

Hi,

I hope you're well and safe!

Can you share some more screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Evan I

This snip pretty much sums up what is in the column for avenue and for street. Using the formula =COUNTIFS({Sheet Nearest Avenue}, >=110, {Sheet Nearest Avenue}, <111) returns 42, which is the number of 110 avenue, but it should be 55 as there are 13 110A Ave. If I change the range to 109 to 111, it gives me 92, but the number should be 121 as it should have the aves of 109, 109A, 110, 110A, but it only counts for the 109 and 110, it doesnt include the 109A, 110A in the range

Paul Newcome

You are going to need another helper column that strips the letter(s) off of the end and converts it to a number.

=VALUE(SUBSTITUTE([Nearest Avenue]@row, "A", ""))

Evan I

I was hoping to not need a helper column. I ended up using =IF(CONTAINS("A", [Nearest Avenue]@row), VALUE(SUBSTITUTE([Nearest Avenue]@row, "A", "")), IF(CONTAINS("B", [Nearest Avenue]@row), VALUE(SUBSTITUTE([Nearest Avenue]@row, "B", "")), [Nearest Avenue]@row)) as its not always an A.

Thanks for the help.

Paul Newcome

Happy to help. 👍️

You can actually shorten that a little bit by removing the IFs and CONTAINSs and just nesting the SUBSTITUTEs. Saves you a bit of work if you have anything else to strip out because you can just add another SUBSTITUTE to the front (inside of the VALUE function of course) and then add what you need to remove to the end.

=VALUE(SUBSTITUTE(SUBSTITUTE([Nearest Avenue]@row, "A", ""), "B", ""))