How to count cells that contains certain letter
Hello,
I have seen different topics how to set up a formula that counts all the cells that contains a certain word, but those formulas does not work when there is one letter next to a number. There will be a day when there would be up to 50 rows with ' D12; N12; D5; D7; N9' and so on in each cell. I want to set up a cell that counts total D's and N's no matter whats the number next to it:
The only formula that works for me right now is:
=COUNTIF([01-Sep]4:[01-Sep]5, "D12")
but it would be long one since I would have to set up for each number (D1; D2; D3 .... up to D17). Wanted to ask you if there is way easier formula for this?
Many thanks!
Answers
-
COUNTIFS along with CONTAINS will do the trick
=COUNTIFS(Searchcolumn:searchcolumn, OR(CONTAINS("D", @cell), CONTAINS("N", @cell)))
-
Hello Dan,
Thank you for your help.
I did not include that part with CONTAINS("N", @cell), since I want those Ns to be counted below the onee that counts Ds. Problem is, your provided formula shows me 0 although there is 4 D's in the selected range..
-
The @cell needs to remain in the OR(CONTAINS("D", @cell)
=COUNTIFS([01-Sep]4:[01-Sep]8, (CONTAINS("D", @cell)))
-
Oh, that is a gamechanger then. It works now, thank you!
-
Cool deal! Glad I could help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!