Scribd Logo
Upload

Welcome to Scribd!

  • 110 views

    Excel SUBTOTAL Function

    Uploaded by

    anita_sandhu729
    The SUBTOTAL function returns a subtotal in a list or database. It uses function numbers 1-11 or 101-111 to specify which function to use for calculating subtotals. Function numbers 1-11 include hidden values while 101-111 ignore hidden values. The SUBTOTAL function is designed for vertical ranges of data and ignores nested subtotals or rows hidden by filters. It will return an error for 3D references.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd

    Excel SUBTOTAL Function

    Uploaded by

    anita_sandhu729
    110 views3 pages
    The SUBTOTAL function returns a subtotal in a list or database. It uses function numbers 1-11 or 101-111 to specify which function to use for calculating subtotals. Function numbers 1-11 include hidden values while 101-111 ignore hidden values. The SUBTOTAL function is designed for vertical ranges of data and ignores nested subtotals or rows hidden by filters. It will return an error for 3D references.

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The SUBTOTAL function returns a subtotal in a list or database. It uses function numbers 1-11 or 101-111 to specify which function to use for calculating subtotals. Function numbers 1-11 include hidden values while 101-111 ignore hidden values. The SUBTOTAL function is designed for vertical ranges of data and ignores nested subtotals or rows hidden by filters. It will return an error for 3D references.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    110 views3 pages

    Excel SUBTOTAL Function

    Uploaded by

    anita_sandhu729
    The SUBTOTAL function returns a subtotal in a list or database. It uses function numbers 1-11 or 101-111 to specify which function to use for calculating subtotals. Function numbers 1-11 include hidden values while 101-111 ignore hidden values. The SUBTOTAL function is designed for vertical ranges of data and ignores nested subtotals or rows hidden by filters. It will return an error for 3D references.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 3

    SUBTOTAL function

    Applies to: Microsoft Office Excel 2007 Print


    Show All Returns a subtotal in a list or database. It is generally easier to create a list with subtotals by using the Subtotal command in the Outline group on the Data tab. Once the subtotal list is created, you can modify it by editing the SUBTOTAL function. Syntax

    SUBTOTAL(function_num, ref1, ref2, ...)


    Function_num is the number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list.

    FUNCTION_NUM (INCLUDES HIDDEN VALUES)


    1 2 3 4 5 6 7 8 9 10 11

    FUNCTION_NUM (IGNORES HIDDEN VALUES)


    101 102 103 104 105 106 107 108 109 110 111

    FUNCTION

    AVERAGE COUNT COUNTA MAX MIN PRODUCT STDEV STDEVP SUM VAR VARP

    Ref1, ref2 Remarks

    are 1 to 254 ranges or references for which you want the subtotal.

    If there are other subtotals within ref1, ref2, (or nested subtotals), these nested subtotals are ignored to avoid double counting. For the function_num constants from 1 to 11, the SUBTOTAL function includes the values of rows hidden by the Hide Rows command under the Hide & Unhide submenu of the Format command in the Cells group on the Home tab. Use these constants when you want to subtotal hidden and nonhidden numbers in a list. For the function_Num constants from 101 to 111, the SUBTOTAL function ignores values of rows hidden by the Hide Rows command. Use these constants when you want to subtotal only nonhidden numbers in a list. The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use. The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal. If any of the references are 3-D references, SUBTOTAL returns the #VALUE! error value. Example The example may be easier to understand if you copy it to a blank worksheet. How to copy an example

    A
    Data

    1 2 3 4

    120 10 150 23 Formula Description (Result)

    5
    =SUBTOTAL(9,A2:A5)Subtotal of the column above using the SUM function (303) =SUBTOTAL(1,A2:A5)Subtotal of the column above using the AVERAGE function (75.75)

    Not what I was looking for

    See Also
    AVERAGE function COUNT function COUNTA function Math and trigonometry functions (reference) MAX function MIN function

    PRODUCT function View More

    You might also like