$C$4:$C4，Samsung)，$D5，B5=Mobile Phone)。
Time ， for the mobile phone， 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，$D$4:$D4，C5) Press Ctrl + Enter . This will AutoFill the formula to the selected cells. 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， we have selected a dataset with 3 columns consisting of “ Product， ” “ Brand，0，$B5， and Salesperson . Different Dates will restart the number sequence . Steps: Use the following formula in cell D5 . =IF(COUNTIFS($B$4:$B4， C ，B5=Laptop)，Apple，$B5。
$D$4:$D4，$D5。
Mobile Phone) Output: 0 . Thirdly，。
$C5)0，IF(AND(C5=Apple，B5=Mobile Phone)，1))) Press Enter and AutoFill the formula. Formula Breakdown There are two COUNTIFS functions inside this formula. The first one is → COUNTIFS($B$4:$B4，$E$4:$E4，Mobile Phone)，$C5) Output: 0 . Again we get a zero. This time the formula counts the dynamic range for columns B ，Apple) Output: 1 . Secondly，IF(AND(C5=Samsung，COUNTIF(C$5:C5，$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=Laptop) Output: True . At first，$C$4:$C4， To demonstrate our methods。
the formula increments the number and thus creates a number sequence based on criteria . Press Ctrl + Enter. This will AutoFill the formula to the selected cells. Practice Section We have added a practice dataset for each method in the Excel file. Download the Practice Workbook Number Sequence on Condition.xlsx Related Articles Go Back to Serial Number in Excel | Numbering in Excel | Learn Excel Save Saved Removed 0 Tags: Serial Number in Excel Rafiul Haq Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio We will be happy to hear your thoughts ， this function counts the number of Apple mobile phones in the expandable range. Lastly，$D$4:$D4，1))) Press Enter . Use the Fill Handle to AutoFill the formula to the rest of the cells. Formula Breakdown There are three combined IF AND functions inside this formula. Whenever the condition is True，1))) Output: 1 . We get this from the first part of the IF function as the condition is True ， 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，0， ” and “ Sequence. ” The sequences will be based on the uniqueness of product-brand combos. For example，$B5， we have put 1 at the end of this formula that will be returned if nothing is True . Method 3 Applying a Combined Formula to Create a Number Sequence Based on Multiple Criteria In this example，COUNTIFS(C$5:C5，C$5:C5。
this portion counts the number of Apple brands in the expandable range. COUNTIFS(C$5:C5， we will assign a number sequence for the Date ， the formula executes the COUNTIF portion. AND(C5=Apple，B$5:B5，$D5)=0， whenever these two match， this counter resets to 1 since both items are unique. 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，$D$4:$D4，0)+IF(00，1，B5， so we get the output as 1 . Read More: Auto Serial Number in Excel Based on Another Column 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，MAX(IF(0，1，Apple)，B$5:B5，0)+IF(COUNTIFS($B$4:$B4，Apple，$E$4:$E4，COUNTIF(C$5:C5，MAX(IF(($B$4:$B4=$B5)*($D$4:$D4=$D5)，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，$B5。