Often, we need to **make a number sequence in Excel**. Things get a little complicated if we want to do it on the basis of a condition. Do not worry, we have got your back. In this article, we will show **4** quick ways to create a **number sequence** in Excel based on criteria.

## Download Practice Workbook

## 4 Handy Approaches to Create a Number Sequence in Excel Based on Criteria

To demonstrate our methods, we have selected a dataset with **3** columns consisting of â€ś**Product**â€ť, â€ś**Brand**â€ť, and â€ś**Sequence**â€ť. Moreover, we will change this a little bit in methods **2** and **3**. Our condition will be the brand name and product name. If there is a duplicate product and brand, then we will increment the **number sequence**. On the other hand, if there is a new entry, then the **number sequence** will start from **1**. In this dataset, notice there are three â€śAppleâ€ť in the Brand column. There are two laptops and one mobile phone for that brand. For the laptop, our number sequence continues, however, for the mobile phone, this counter resets to **1**.

### 1. Using COUNTIF Function to Create a Number Sequence in Excel Based on Criteria

In this section, we will use ** the COUNTIF function** to create a

**number sequence**based on a criterion, which is the brand name.

**Steps:**

- At first, select the cell range
and type the following formula.*D5:D10*

`=COUNTIF(C$5:C5,C5)`

This function counts the number of â€śAppleâ€ť in the brand column. The first part of the formula refers to a dynamic range.

- Then, press
. This will*CTRL+ENTER*the formula to the selected cells.*AutoFill* - Lastly, we can observe that there are:
- Three Samsung mobile phones.
- Two Apple laptops.
- One Lenovo laptop.
- Thus, this formula creates a
**number sequence**based on criteria.

**Read More: ****How to Create a Number Sequence in Excel Without Dragging**

### 2. Inserting Nested IF Function to Create a Number Sequence in Excel Based on Criteria

For the second approach, we will use the ** IF**,

**,**

*AND***, and**

*COUNTIF***functions inside a**

*COUNTIFS***formula to make a number sequence based on conditions.**

*Nested IF***Steps:**

- To begin with, type the following formula in cell
.*D5*

`=IF(AND(C5="Apple",B5="Laptop"),COUNTIF(C$5:C5,"Apple"),IF(AND(C5="Samsung",B5="Mobile Phone"),COUNTIF(C$5:C5,"Samsung"),IF(AND(C5="Apple",B5="Mobile Phone"),COUNTIFS(C$5:C5,"Apple",B$5:B5,"Mobile Phone"),1)))`

- Then, press
.*ENTER* - Next, use the
to*Fill Handle*the formula to the rest of the cells.*AutoFill*

**Formula Breakdown**

- There are three combined
functions inside this formula. Whenever the condition is True, the formula executes the*IF AND*portion.*COUNTIF* **AND(C5=â€ťAppleâ€ť,B5=â€ťLaptopâ€ť)****Output: True**.- At first, this portion checks whether the values from cells
and*C5*match our defined strings. If matches then it will return True.*B5*

**COUNTIF(C$5:C5,â€ťAppleâ€ť)****Output: 1**.- Secondly, this portion counts the number of Apple brands in the expandable range.

**COUNTIFS(C$5:C5,â€ťAppleâ€ť,B$5:B5,â€ťMobile Phoneâ€ť)****Output: 0**.- Thirdly, this function counts the number of Apple mobile phones in the expandable range.

- Lastly, we have put
**1**at the end of this formula that will be returned if nothing is**True**.

- Finally, we will achieve our goal of creating a
**number sequence**based on criteria. Moreover, the final step will look like this.

**Read More: ****How to Autofill in Excel with Repeated Sequential Numbers**

**Similar Readings**

**How to Add Numbers 1 2 3 in Excel (2 Suitable Cases)****Create a Formula for Serial Number in Excel (7 Methods)****How to Add Automatic Serial Number with Formula in Excel**

### 3. Applying Combined Formula to Create a Number Sequence Based on Multiple Criteria

In this third approach, we will use the ** IF**,

**, and**

*COUNTIFS***functions to create a combined formula to make a**

*MAX***number sequence**based on

**criteria**. In this example, we will assign a number sequence for the

**Date**,

**Time**, and

**Salesperson**. Where the different

**Dates**will restart the

**number sequence**.

**Steps:**

- At first, type the following formula in cell
.*D5*

`=IF(COUNTIFS($B$4:$B4,$B5,$D$4:$D4,$D5)=0,1,MAX(IF(($B$4:$B4=$B5)*($D$4:$D4=$D5),$E$4:$E4,0)+IF(COUNTIFS($B$4:$B4,$B5,$D$4:$D4,$D5,$C$4:$C4,$C5)>0,0,1)))`

- Next, press
and*ENTER*the formula.*AutoFill*

**Formula Breakdown**

- Firstly, there are two
functions inside this formula. Let us look at them first.*COUNTIFS* - The first one is â†’
**COUNTIFS($B$4:$B4,$B5,$D$4:$D4,$D5)****Output: 0**.- This function counts the dynamic range with the same time and same salesperson.

- The second one is â†’
**COUNTIFS($B$4:$B4,$B5,$D$4:$D4,$D5,$C$4:$C4,$C5)****Output: 0**.- Again we get a zero. This time the formula counts the dynamic range for columns
,*B*, and*C*.*D*

**($B$4:$B4=$B5)*($D$4:$D4=$D5)****Output: 0**.- Here
**1**means**True**and**0**means**False**. It has given us the**False**value.

- Then, our formula reduces to â†’
**IF(0=0,1,MAX(IF(0,$E$4:$E4,0)+IF(0>0,0,1)))****Output: 1**.- We get this from the first part of the
function as the condition is*IF***True**, so we get the output as**1**.

**Read More: ****Auto Serial Number in Excel Based on Another Column**

### 4. Using COUNTIFS Function to Create a Number Sequence Based on Criteria

In this last method, we will use the** COUNTIFS **function to create a

**number sequence**based on conditions.

**Steps:**

- At first, select the cell range
and type the following formula.*D5:D10*

`=COUNTIFS(B$5:B5,B5,C$5:C5,C5)`

There are two conditions in this formula. The first one looks for the product type and the second one looks for the brand name. Now, whenever these two match, the formula increments the number and thus creates a **number sequence** based on **criteria**.

- Lastly, press
. This will*CTRL+ENTER*the formula to the selected cells.*AutoFill*

## Practice Section

We have added a practice dataset for each method in the **Excel** file. So that, you can follow along with our methods easily.

## Conclusion

We have shown you **4** handy approaches to how to create a **number sequence** in Excel based on **criteria**. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Moreover, you can visit our site **ExcelDemy** for more Excel-related articles. Thanks for reading, keep excelling!