Using Countif in place of Autonumber - formatting question!

I'm creating a ticketing system with multiple prefixes. Because of the multiple prefixes on one sheet, II opted to create a nested formula to number the tickets based on the various types of requests. Here is the formula:

=VLOOKUP([Request Type]@row, {Ticket Assignment}, 2, false) + (COUNTIFS([Request Type]$1:[Request Type]@row, [Request Type]@row, Created$1:Created@row, YEAR(@cell) = YEAR(Created@row)))

The problem I'm having is that I'm getting tickets with a single digit (I.e., "IT-1") and I'd like it to be numbers to appear with 3 digits (I.e., "IT-001")

Is there a way to format the formula to increase the number of digits shown in the formula's results?

Find more posts tagged with

Sheets

Formulas

Reports

Project Management

Accepted answers

Mike TV

@JBS27

I'm sure there's more than one way to accomplish this. Here's my shot at it:

First you set up a helper column such as the one I have in Column3 with this formula which tells you the length of the value in Column2:

=LEN([Column2]@row)

Then you set your final result formula such as the one I have in Column4 like so:

=IF([Column3]@row = 4, SUBSTITUTE([Column2]@row, "IT-", "IT-00"), IF([Column3]@row = 5, SUBSTITUTE([Column2]@row, "IT-0", "IT-00"), [Column2]@row))

If you end up with any rows that do not have a properly formatted number, let me know and we can work out the bugs. After it's all set up, you can hide Column2 and Column3 on the sheet.

All comments

Mike TV

@JBS27

I'm sure there's more than one way to accomplish this. Here's my shot at it:

First you set up a helper column such as the one I have in Column3 with this formula which tells you the length of the value in Column2:

=LEN([Column2]@row)

Then you set your final result formula such as the one I have in Column4 like so:

=IF([Column3]@row = 4, SUBSTITUTE([Column2]@row, "IT-", "IT-00"), IF([Column3]@row = 5, SUBSTITUTE([Column2]@row, "IT-0", "IT-00"), [Column2]@row))

If you end up with any rows that do not have a properly formatted number, let me know and we can work out the bugs. After it's all set up, you can hide Column2 and Column3 on the sheet.

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.