![]() ![]() In this case, SUBTOTAL includes values in hidden rows. Here the function uses Calc's COUNT function to count the number of scores (COUNT ignores strings and so the asterisk in cell B5 is ignored). As there are no hidden rows in the data, the formula =SUBTOTAL(105 B2:B13) returns the same value.įor the following examples the same data table is used but row 2 is hidden (by right-clicking on the row number and selecting Hide Rows from the context menu). Here the function uses Calc's MIN function to determine the minimum score value. ![]() As there are no hidden rows in the data, the formula =SUBTOTAL(104 B2:B13) returns the same value. Here the function uses Calc's MAX function to determine the maximum score value. As there are no hidden rows in the data, the formula =SUBTOTAL(103 B2:B13) returns the same value. Here the function uses Calc's COUNTA function to count the number of scores (COUNTA includes strings and so the asterisk in cell B5 is counted). As there are no hidden rows in the data, the formula =SUBTOTAL(102 B2:B13) returns the same value. The following examples are based on the data in the above table. The asterisk (*) in cell B5 indicates that James was absent and unable to take the test. The students' names are listed in alphabetical order in cells A2:A13 and their scores are listed in cells B2:B13. See their individual pages for more details about each of the eleven possible subtotal functions.Ī class of 12 students were set a module test and their scores are entered in a spreadsheet as shown in the table below.Hiding columns has no impact on the results produced by the SUBTOTAL function.This provides for interoperability with Microsoft Excel. However, it is possible to provide multiple ranges to SUBTOTAL by passing the second range as the third argument, the third range as the fourth argument, and so on. The syntax given in the #Syntax: section above is aligned with that in Calc's Function Wizard.If Function is not in either the range or the range, then SUBTOTAL reports an invalid argument error (Err:502).If Function is a non-integer value, then SUBTOTAL truncates it to an integer. ![]() If Function in non-numeric, then SUBTOTAL reports a #VALUE! error.The following conditions (including errors) may be encountered: An inline array of real numbers (such as ) returns 120.For example, if cell D1 contains the number 123, the formula =SUBTOTAL(1 D1) returns 123. A reference to a single cell containing a real number.For example, the formula =SUBTOTAL(1 3) returns 3. A real number, or an expression that evaluates to a real number.In addition, the following forms for the Range argument do not cause an error but are not likely to be particularly useful: The name of a database range, comprising cells containing real numbers.The name of a named range, comprising cells containing real numbers.A simple reference to a cell range containing real numbers (for example, A1:A25).Range will usually take one of the following forms: Range gives the set of real numbers for which the subtotal is to be calculated. Function must lie either in the range or in the range, with the meaning of each possible value given in the following table. Returns a number that is the required subtotal - see the page that describes the selected subtotal function for more details about the nature of the returned value in each case.įunction is an integer, or a reference to a cell containing that integer, that is an index indicating which function should be used for calculating the subtotal and whether hidden rows should be included or excluded. More details can be found in the system help by searching for "subtotals tool". SUBTOTAL can optionally include or exclude data from rows within the data range that have been manually hidden.Ĭalc provides a powerful and more comprehensive alternative to the SUBTOTAL function, accessed by selecting Data ▸ Subtotals on the Menu bar. If any row within the data range is filtered out, its data is ignored by SUBTOTAL. If the data range processed by SUBTOTAL already contains any subtotals, these are ignored to avoid counting values twice. SUBTOTAL is intended for use with a simple table of data occupying a rectangular spreadsheet area, with the data organized vertically in columns. Calculates a subtotal using a selected mathematical function. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |