The first range is B2:B4 and the second range is B11:B13. In the following example, I have used two different ranges in the SUBTOTAL function. March record.Įnter various values within the limit in B15 cell and see how it is updated in the B16 cell. I have used the following generic formula in B16 cell:Īs you enter a value in the B15 cell from 1-11 or 101 to 111, the respective formula will be applied to the B2:B7 range that contains the number of sales.įor seeing the difference between hidden and unhidden row values, I have hidden the third row that contains 150 value i.e. You may interact with it right here or download at your system. =SUBTOTAL(5,C2:C6) Play with various function values in interactive Excel sheet onlineīelow, you can see an interactive Excel sheet that contains the same data as used in above examples. You can see, the month of March record is kept hidden (compare it with above graphic).

The following example uses the Amount column in our example sheet to get the average, count, max, and min values for a given range. The example of getting average, max and min values List of values that do not include hidden cells in SUBTOTAL function: List of Function_num argument values with functionsįollowing are the numbers and respective functions that you may specify in the SUBTOTAL function: The SUBTOTAL allows up to 254 references that you want to get SUBTOTAL. Optionally, you may also provide ref2 for the second reference or range.I used B2:B5 in above example, so SUBTOTAL returned the sum of that range only. The ref1 argument represents the reference, range or named range etc.This is the required argument in the SUBTOTAL function. See the list below for learning which function each of these numbers represents. The value 1 is for AVERAGE function, 2 for COUNT, and 9 for SUM function. You may use 1-11 or 101-111 values in this argument. Function_num specifies the function to use for the given range in SUBTOTAL function.On the basis of this example, let us have a look at the general syntax of the SUBTOTAL function. The value 9 means return the sum of the given range. We will get the sum of B2:B5 range by using the SUBTOTAL function. For example, the value 1 is for returning average for the specified list or database and value 9 means return the SUM.īefore explaining the syntax of SUBTOTAL, let us have a look at a simple example of using the SUBTOTAL function for getting the sum.įor that, we have an Excel sheet with three columns that contain Month, Number of Sales and Amount. In the SUBTOTAL function, you have to enter the value of the function that specifies what is required as the subtotal.

for the specified list, named range or database. The SUBTOTAL function in Excel is used to return the subtotal in terms of sum, average, count, max, min etc.

Multiple subtotals in excel