Counting the amount of numbers found in a column with series of numbers

Hello,

I'm currently trying to create a formula for a column "Repeats" that counts the times a number in the adjacent cell [Split Cells] is found within another column [Depot Name or Site] where the rows contain a series of numbers. If found, I want the total amount of times that number is found.

=IF((CONTAINS([Split Cells]@row, [Depot Name or Site]:[Depot Name or Site])), COUNTIF([Depot Name or Site]:[Depot Name or Site], CONTAINS([Split Cells]@row, [Depot Name or Site]:[Depot Name or Site])))

This is the formula I was using, however it returns "0" regardless of the value @row, so I'm sure something is wrong. Any help would be highly appreciated.

Find more posts tagged with

Accepted answers

All comments

Itai Perez

Hey @sawuzie,

Can you please share a screenshot of the sheet?

Andrée Starå

Hi @sawuzie

I hope you're well and safe!

Can you share some 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 weekend!

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!

sawuzie

hello @Itai @Andrée Starå

this is an example of what im seeing

Andrée Starå

@sawuzie

Try something like this.

=COUNTIFS([Depot Name or Site]:[Depot Name or Site], CONTAINS([Split Cells]@row, @cell))

Did that work/help?

✅Remember! 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!

sawuzie

@Andrée Starå Yes that worked thank you!

Can I also use this function to create a column that generates the applicable dates of the cells counted, to be able to join the unique dates of those cells into a new column?

I would like the dates of applicable rows from [Date of Excursion Identification] to be listed in the next column over [Cumulative Excursion Dates], with a comma as a delimiter.

I tried my best in explaining, hopefully you can decipher what I am trying to ask ha ha. Any help you can offer is very appreciated!

Andrée Starå

@sawuzie

Excellent!

You're more than welcome!

Try this.

=JOIN(COLLECT([Date of Excursion Identification]:[Date of Excursion Identification], Repeats:Repeats, >0), ", ")

Did it work?

sawuzie

@Andrée Starå This is great! I just noticed that this formula returns the same value for every cell, can this be used for @row values of Repeats instead?

thank you

Andrée Starå

@sawuzie

I'm always happy to help!

My mistake. I forgot to include the check for the Split Cells.

Try this.

=JOIN(COLLECT([Date of Excursion Identification]:[Date of Excursion Identification], Repeats:Repeats, >0, [Split Cells]:[Split Cells], [Split Cells]@row), ", ")

Did it work?

sawuzie

@Andrée Starå For some reason, it doesn't seem to be pulling the right amount of dates into the column.

If the amount of Repeats is 5, it should return 5 dates, correct?

Thank you!

sawuzie

@Andrée Starå oh I see! The search range is the [Split Cells] column, when it should be the [Depot Name or Site], and then the Split Cells determines what dates are being pulled. I attempted to adjust this myself with not much luck.

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.