Formula Errors

How to Create Sequential Audit IDs in Excel with IF and COUNTIF

Rana Garcia Sep 30, 2026 868 views

Question details

The user needs to generate unique, sequential identifiers (such as OFI24-01 or NC24-01) by combining a category code, a two-digit year, and a running count.

How to Create Sequential Audit IDs in Excel Using IF and COUNTIF

Product: Excel
Device & OS: not provided
Scenario: Setting up an audit log or tracking sheet where each line item requires a custom, automatically incrementing ID based on its specific category and the current year.
Observed behavior: The identifier needs to correctly format the year and increment the count accurately, depending on whether the year reference cell contains a simple number or a formatted date.

Before you start

Ensure your dataset is organized with the category codes in a single column (e.g., Column A) and designate a specific reference cell (e.g., N1) to hold the current year or date.

Solution 1Recommended

Generate IDs When the Year Reference is a Number

Use this formula if your reference cell contains a plain 4-digit (e.g., 2024) or 2-digit (e.g., 24) number.

This formula uses the MOD function to extract the last two digits of a numerical year. It then combines it with the category name and a running count calculated by the COUNTIF function.

Select the target cell

Click on the cell where you want the first sequential Audit ID to appear (for example, B3).

Enter the formula

Type the following formula: =IF(A3="","",A3&MOD($N$1,100)&"-"&TEXT(COUNTIF($A$3:$A3,$A3),"00"))

Apply to the column

Press Enter to generate the ID, then click and drag the fill handle from the bottom-right corner of the cell downwards to apply the formula to the rest of your list.

Generate IDs When the Year Reference is a Number

Formula Breakdown: The MOD($N$1,100) segment dynamically grabs the '24' from '2024'. The COUNTIF($A$3:$A3,$A3) segment counts how many times the specific category has appeared up to that row, ensuring the numbering resets for different categories.

Solution 2

Generate IDs When the Year Reference is a Date

Use this formula variation if your year reference cell contains a standard date format rather than a plain number.

Generate Sequential IDs Effortlessly in WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical and statistical functions like IF, COUNTIF, and TEXT. You can easily manage audit logs, track categories, and generate complex dynamic identifiers in a familiar, fast interface.

1. Open your tracking sheet: Launch WPS Spreadsheet and open your audit or tracking workbook.
2. Set up your reference cells: Ensure your categories are listed in Column A and place your year or date reference in cell N1.
3. Input the formula: Select the target ID cell and paste the provided IF and COUNTIF formula based on your year format.
4. Drag to fill: Press Enter and double-click or drag the fill handle to automatically number your entire list.

Fully compatible with Microsoft Excel formulas and functionsLightweight and fast for handling large audit logs and tracking sheetsBuilt-in data validation to ensure category input consistencyFree to use across Windows, Mac, Linux, and mobile devices

Frequently Asked Questions

Why does my sequential ID start over at 1 for different categories?

This is the intended behavior of the formula. The COUNTIF function looks specifically at the category in that row and counts how many times it has appeared previously. This creates a unique running count for each individual category.

How do I change the running count to three digits instead of two?

To change the format from two digits (e.g., -01) to three digits (e.g., -001), modify the TEXT function at the end of the formula. Change TEXT(COUNTIF(...),"00") to TEXT(COUNTIF(...),"000").

Can I hardcode the year into the formula instead of referencing a cell?

Yes. If you don't want to use a reference cell like N1, you can replace the MOD($N$1,100) or TEXT($N$1,"yy") portion of the formula with the hardcoded text string "24". The formula would look like: =IF(A3="","",A3&"24-"&TEXT(COUNTIF($A$3:$A3,$A3),"00")).

Why is my formula returning a blank cell?

The formula begins with IF(A3="","",...), which tells Excel to leave the ID cell blank if the category cell (A3) is empty. Make sure there is data in the referenced category cell to generate an ID.

Excel & Data Problems