Count file names only

I'm trying to create a unique ID for documents. I would like to generate a unique number for each Category (in "Abv") but I'm struggling to not count the parent rows.

My current formula is: =SUM(COUNTIFS(Auto:Auto, <Auto@row, Abr:Abr, Abr@row) + COUNTIFS(Auto:Auto, Auto@row, Abr:Abr, Abr@row))

The formula abode had been in "Count" but was counting the green and bolded rows too (which are folders, not files). In the yellow box is manually keyed in how I'd like the formula to create the numbering so the "New Document #" column will produce a unique number that starts at 1 for each Category eg. "MO", "SE"

Find more posts tagged with

Formulas

Accepted answers

Mark Cronk

Good afternoon, You could add a helper column to flag or a checkbox parent rows. Then use a COUNTIFS() that contains the helper column criteria -- Helper:Helper, =0

Mark

All comments

Mark Cronk

Good afternoon, You could add a helper column to flag or a checkbox parent rows. Then use a COUNTIFS() that contains the helper column criteria -- Helper:Helper, =0

Mark

Adrienne Van Halem

https://community.smartsheet.com/discussion/comment/269488#Comment_269488

Thanks so much! I added a helper column with =IF(COUNT(CHILDREN()) > 0, 1, 0)

and then changed my Count column to be

=SUM(COUNTIFS(Auto:Auto, <Auto@row, Abr:Abr, Abr@row, [Helper2]:[Helper2], =0) + COUNTIFS(Auto:Auto, Auto@row, Abr:Abr, Abr@row))

It still returns a 1 on the Parent folders but my statement in the Doc # column just doesn't return it.

Mark Cronk

Hi again @Adrienne Van Halem,

In your count column try:

=if(Helper2@row=1,"", COUNTIFS($[Abr]$1:[Abr]@row, =[Abr]@row, $[helper2]$1:[helper]@row, =0))

Your helper2 formula should be =IF(Count(Children())>0,1,0)

Your count column should return a series of numbers starting with 1 for each Abr category (MO & SE).

Work?

Mark

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.