How 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.” The sequences will be based on the uniqueness of product-brand combos. For example, for the mobile phone, this counter resets to 1 since both items are unique.

Create a Number Sequence in Excel Based on Criteria 1

Method 1 – Using the COUNTIF Function to Create a Number Sequence in Excel Based on Criteria

Steps:

Select the cell range D5 and use the following formula.

=COUNTIF(C$5:C5,C5)

Create a Number Sequence in Excel Based on Criteria 2

Press Ctrl + Enter. This will AutoFill the formula to the selected cells.

Create a Number Sequence in Excel Based on Criteria 3

Method 2 – Inserting a Nested IF Function to Create a Number Sequence in Excel Based on Criteria

Steps:

Use 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)))

Create a Number Sequence in Excel Based on Criteria 4

Press Enter.
Use the Fill Handle to AutoFill the formula to the rest of the cells.

Create a Number Sequence in Excel Based on Criteria 5

Formula Breakdown

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

Sample Dataset

Method 3 – Applying a Combined Formula to Create a Number Sequence Based on Multiple Criteria

In this example, we will assign a number sequence for the Date, Time, and Salesperson. Different Dates will restart the number sequence.

Steps:

Use 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)))

Create a Number Sequence in Excel Based on Criteria 6

Press Enter and AutoFill the formula.

Create a Number Sequence in Excel Based on Criteria

Formula Breakdown

There are two COUNTIFS functions inside this formula.
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, C, and 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.
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 IF function as the condition is True, so we get the output as 1.

Method 4 – Using the COUNTIFS Function to Create a Number Sequence Based on Criteria

Steps:

Select the cell range D5:D10 and insert the following formula.

=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.

Using COUNTIFS Function