Subtotal Formula in Excel for Serial Number (3 Suitable Examples)

Microsoft Excel includes the SUBTOTAL function by default. This function provides the opportunity to perform a group of Excel function operations. If you are looking for some special tricks to use the subtotal formula for the serial number in Excel, you’ve come to the right place. There are three ways to use the subtotal formula for serial number in Excel. This article will discuss three suitable ways of using the subtotal formula for serial number. Let’s follow the complete guide to learn all of this.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try yourself while you go through the step-by-step process.


3 Suitable Examples to Use Subtotal Formula for Serial Number in Excel

In the following section, we will use three effective and tricky ways to use the subtotal formula for the serial number in Excel. This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.


1. Use of SUBTOTAL Function for Serial Number

Here, we will demonstrate how to use the subtotal formula for the serial numbers in Excel.  Let us first introduce you to our Excel dataset so that you are able to understand what we are trying to accomplish with this article. The following dataset contains Student’s name and their corresponding exam score. One effective way to create automatic serial numbers is to use the SUBTOTAL function. The SUBTOTAL function returns a subtotal in a list. It gives users the opportunity to perform different operations in specific groups in a list. The SUBTOTAL function has 2 required arguments. You need to insert the function number in the first argument and the reference in the second argument. Let’s walk through the steps to see how we can implement the SUBTOTAL function to add serial numbers.

Use of SUBTOTAL Function for Serial number

📌 Steps:

  • In the beginning, select Cell B5 and type the formula below.

=SUBTOTAL(3,$C$5:C5)

  • Next, press Enter.

Subtotal Formula in Excel for Serial Number

  • Now, drag the Fill Handle icon to fill the other cells with the above formula.
  • Therefore, you will get the following serial number.

  • In the following image, we can see that no new serial is created for the cells where there are no corresponding values for the related cells if we drag the Fill Handle icon into the next few cells.

Subtotal Formula in Excel for Serial Number

  • Now, we are going to filter the data to show the only visible column serial number in the dataset.
  • To do this, we have to select the range of cells and press Ctrl+Shift+L.

  • This will instantly add a filter to the data as shown below.

Subtotal Formula in Excel for Serial Number

  • Now, click on the Math column drop-down icon, select Number Filters, and finally select Greater Than option.

  • When the Custom AutoFilter window appears, type your required number in the is greater than a box.
  • Then, click on OK.

Subtotal Formula in Excel for Serial Number

  • You will therefore get the following column when you filter the Math column data based on a score greater than 70. The serial number is automatically generated when the column is reduced according to the criteria.

Use of SUBTOTAL Function for Serial number

Read More: How to Increment Row Number in Excel Formula (6 Handy Ways)


Similar Readings


2. Combining SUBTOTAL with IF Function

Here,  we will use another effective way to create serial numbers by applying SUBTOTAL and IF functions. The following dataset contains Student’s name and their corresponding exam score. Let’s walk through the following steps to create serial numbers.

Combining SUBTOTAL with IF Function

📌 Steps:

  • In the beginning, select Cell B5 and type the formula below.

=IF(C5="","",SUBTOTAL(3,$C$5:C5))

  • Next, press Enter.

Subtotal Formula in Excel for Serial Number

  • Now, drag the Fill Handle icon to fill the other cells with the above formula.
  • Therefore, you will get the following serial number.

  • Now, we are going to filter the data to show the only column serial number in the dataset.
  • To do this, we have to select the range of cells and press Ctrl+Shift+L.

Subtotal Formula in Excel for Serial Number

  • This will instantly add a filter to the data as shown below.

  • Now, click on the Math column drop-down icon, select Number Filters, and finally select Greater Than option.

Subtotal Formula in Excel for Serial Number

  • When the Custom AutoFilter window appears, type your required number in the is greater than box.
  • Then, click on OK.

  • You will therefore get the following column when you filter the Math column data based on a score greater than 80. The serial number is automatically generated when the column is reduced according to the criteria.

Combining SUBTOTAL with IF Function

 How Does the Formula Work?

  • SUBTOTAL(3,$C$5:C5)

In this formula, we have used the SUBTOTAL function. The first argument indicates the function number. And function number 3 refers to the COUNTA function. So, it will count the cells in the range $C$5:C5.

  • IF(C5=””,””,SUBTOTAL(3,$C$5:C5))

If Cell C5 is empty, the IF function will return blank, otherwise, it will return the SUBTOTAL function value.

Read More: How to Create a Number Sequence with Formula in Excel


3. Applying MAX and SUBTOTAL Functions

Here,  we will use another effective way to create serial numbers by applying SUBTOTAL and MAX functions. The following dataset contains Student’s name and their corresponding exam score. Let’s walk through the following steps to create serial numbers.

Applying MAX and SUBTOTAL Functions

📌 Steps:

  • In the beginning, select Cell B5 and type the formula below.

=MAX(SUBTOTAL(3,D$5:D5))

  • Next, press Enter.

Subtotal Formula in Excel for Serial Number

  • Now, drag the Fill Handle icon to fill the other cells with the above formula.
  • Therefore, you will get the following serial number.

  • We can see that no new serial is created for the cells where there are no corresponding values for the related cells if we drag the Fill Handle icon into the next few cells.

Subtotal Formula in Excel for Serial Number

  • Now, we are going to filter the data to show the only column serial number in the dataset.
  • To do this, we have to select the range of cells and press Ctrl+Shift+L.

  • This will instantly add a filter to the data as shown below.

Subtotal Formula in Excel for Serial Number

  • Now, click on the Math column drop-down icon, select Number Filters, and finally select Greater Than option.

  • When the Custom AutoFilter window appears, type your required number in the is greater than box.
  • Then, click on OK.

Subtotal Formula in Excel for Serial Number

  • You will therefore get the following column when you filter the Math column data based on a score greater than 70. The serial number is automatically generated when the column is reduced according to the criteria.

Applying MAX and SUBTOTAL Functions

How Does the Formula Work?

  • SUBTOTAL(3,$C$5:C5)

In this formula, we have used the SUBTOTAL function. The first argument indicates the function number. And function number 3 refers to the COUNTA function. So, it will count the cells in the range $C$5:C5.

  • MAX(SUBTOTAL(3,D$5:D5))

The MAX function will return the largest value in the list of arguments.

Read More: How to Add Numbers 1 2 3 in Excel (2 Suitable Cases)


💬 Things to Remember

When you are creating serial numbers using the subtotal formula, you need to keep certain things in mind.

✎ You should keep in mind that excel doesn’t consider hidden values the same as the filtered-out values. So, when you hide any rows the serial will not generate automatically.

✎ When using multiple functions in one formula, you need to put the parenthesis carefully, otherwise, the formula will not be executed.


Conclusion

That’s the end of today’s session. I strongly believe that from now you may be able to create serial number by using the subtotal formula in Excel. If you have any queries or recommendations, please share them in the comments section below.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Saquib

Saquib

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo