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!

Find more posts tagged with

Accepted answers

All comments

Dan W

COUNTIFS along with CONTAINS will do the trick

=COUNTIFS(Searchcolumn:searchcolumn, OR(CONTAINS("D", @cell), CONTAINS("N", @cell)))

Patrikas

https://community.smartsheet.com/discussion/comment/332531#Comment_332531

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

Dan W

The @cell needs to remain in the OR(CONTAINS("D", @cell)

=COUNTIFS([01-Sep]4:[01-Sep]8, (CONTAINS("D", @cell)))

Patrikas

Oh, that is a gamechanger then. It works now, thank you!

Dan W

Cool deal! Glad I could help!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.