Help with Counting for specific value within cells

I'm trying to get a count of the number of cells (within the column Card_StartDate) that have a date of less than 12/31/21, but only when an adjacent column (LaneTitle) has cells with content that contains 'Parent' (parent can be anywhere within those cells). I'm completely lost on this and can't find what I'm looing for in community.

I appreciate any help you can provide!

Here's what I have thus far, to start the formula (which is accurate):

=COUNTIF([Card_StartDate]:[Card_StartDate], "<=12/31/21")

Where I'm lost is trying to pull the count for only the data that has the word parent within.

=FIND("Parent", @cell) > 0

Find more posts tagged with

Formulas

Accepted answers

Kelly Moore

The CONTAINS function is what you need to look for text anywhere within a cell. It's not an exact match function and is not case sensitive.

=COUNTIFS([Card_StartDate]:[Card_StartDate], <=DATE(2021,12, 31), LaneTitle:LaneTitle, CONTAINS("Parent", @cell))

All comments

Kelly Moore

Hey @Brooke Clem

Try this

=COUNTIFS([Card_StartDate]:[Card_StartDate], <=12/31/21, LaneTitle:LaneTitle, "Parent")

cheers,

Kelly

Brooke Clem

Thank you for your help, but This gives me an #InvalidOperation.

=COUNTIFS([Card_StartDate]:[Card_StartDate], <=12/31/21, LaneTitle:LaneTitle, "Parent")

Also - "Parent" could be a word within the cell(s). I somehow need to pull data for the 'LaneTitle' column that contains "Parent" anywhere within each cell.

Does that help?

Kelly Moore

Hey Brooke

oops, my bad

=COUNTIFS([Card_StartDate]:[Card_StartDate], <=DATE(2021,12, 31), LaneTitle:LaneTitle, "Parent")

Brooke Clem

Thank you KDM! This is fantastic!

How do I change "Parent" to asking for cells that contain "Parent" anywhere within the cell(s), not just solely "Parent"?

Kelly Moore

The CONTAINS function is what you need to look for text anywhere within a cell. It's not an exact match function and is not case sensitive.

=COUNTIFS([Card_StartDate]:[Card_StartDate], <=DATE(2021,12, 31), LaneTitle:LaneTitle, CONTAINS("Parent", @cell))

Brooke Clem

This is PERFECTION! Thank you so much!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.