How to use Subtotal Formula in Excel – Excel Avon

Arguments

function_num –  A number that specifies which function to use in calculating subtotals within a list.

ref1 – All cell ranges to be included in the calculation

[ref2] – [optional] A named range or reference to subtotal.

How to Use Subtotal Formula 

You can download used excel file from here>>>

Formula For grand total(Visible Items)

So now we use the Subtotal formula in the example and let’s see how it will work. We have created some data in which everyone will use the total formula as the cell can see below, in it some fruits and vegetables will find their average and sum. 

So now we will apply the formula For grand total(Visible Items) : =SUBTOTAL(9,E8:E15)

Note : For addition, the Function number will use 9 For visible data & 109 for Ignore hidden.

Now You Can See  grand total(visible Items) of fruits.

Formula For Average Unit Price (Visible Items)

Now we will use the formula Average Unit Price (Visible Items) Function No. 1 For Visible Item We will use 101 for Hidden Number.

So now we will apply the formula For Average Unit Price (Visible Items) : =SUBTOTAL(1,D8:D12)

If we remove the vegetable,

The grand total and average of the vegetable is removed.

You can download used excel file from here>>

Note 

• In filtered lists, SUBTOTAL always ignores values in hidden rows, regardless of function_num.
• SUBTOTAL works with vertical data. In horizontal ranges, values in hidden columns are always included.
• Blank cells and cells containing non-numeric values are ignored by subtotal function during the calculation

Now you can try change lookup image value. So I hope you have understood this formula and for more information you can follow us on Twitter, Instagram, LinkedIn and YouTube as well.

You can also see well explained video here

You can also learn SUM Formula