{"id":431,"date":"2025-07-15T13:34:46","date_gmt":"2025-07-15T13:34:46","guid":{"rendered":"https:\/\/functiontranslator.com\/2021\/02\/12\/subtotal\/"},"modified":"2021-02-13T11:36:32","modified_gmt":"2021-02-13T11:36:32","slug":"subtotal","status":"publish","type":"post","link":"https:\/\/functiontranslator.com\/en\/subtotal\/","title":{"rendered":"SUBTOTAL"},"content":{"rendered":"

Today, we’ll delve into the SUBTOTAL function, available in both Microsoft Excel and Google Sheets. This versatile function performs a variety of operations on a set of cells, including sum, average, count, max, min, and more. A key feature of SUBTOTAL is its ability to disregard other SUBTOTAL calculations within the same range.<\/p>\n

Basics of SUBTOTAL Function<\/h2>\n

The syntax for the SUBTOTAL function is consistent across both Excel and Google Sheets:<\/p>\n

=SUBTOTAL(function_num, ref1, [ref2], ...)<\/code><\/pre>\n
    \n
  • function_num<\/code>: Specifies the operation to execute. The values range from 1-11 in Excel and 1-9 in Google Sheets.<\/li>\n
  • ref1<\/code>, ref2<\/code>, etc.: Refers to one or more cell ranges to which the subtotal operation will be applied.<\/li>\n<\/ul>\n
    Examples of Using SUBTOTAL Function<\/h2>\n
    Here are some practical applications of the SUBTOTAL function:<\/p>\n\n\n\n\n\n
    Task<\/th>\nExcel Formula<\/th>\nGoogle Sheets Formula<\/th>\n<\/tr>\n
    Calculate the sum of visible cells<\/td>\n=SUBTOTAL(9, A1:A10)<\/code><\/td>\n=SUBTOTAL(109, A1:A10)<\/code><\/td>\n<\/tr>\n
    Calculate the average of visible cells<\/td>\n=SUBTOTAL(1, B1:B10)<\/code><\/td>\n=SUBTOTAL(101, B1:B10)<\/code><\/td>\n<\/tr>\n
    Count visible cells with numbers<\/td>\n=SUBTOTAL(2, C1:C10)<\/code><\/td>\n=SUBTOTAL(102, C1:C10)<\/code><\/td>\n<\/tr>\n<\/table>\n
    In these examples, the function_num<\/code> determines the specific operation to be performed on the data range. For example, 9 and 109 are used for summing, 1 and 101 for averaging, and 2 and 102 for counting. Note the variation in function codes between Excel and Google Sheets.<\/p>\n
    Benefits of SUBTOTAL Function<\/h2>\n
    The SUBTOTAL function proves particularly valuable when working with filtered data, as it computes results based solely on visible cells. This is especially useful in ignoring any rows hidden due to filtering, offering more accurate outcomes in such cases.<\/p>\n
    This guide should provide a comprehensive understanding of the SUBTOTAL function\u2019s capabilities and demonstrate its effectiveness in processing calculations in Excel and Google Sheets.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Returns a subtotal in a list or database<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[198],"tags":[],"class_list":["post-431","post","type-post","status-publish","format-standard","hentry","category-math-and-trigonometry"],"acf":[],"yoast_head":"\nExcel SUBTOTAL Function \u2013 How It Works, Formula Examples and Syntax<\/title>\n<meta name=\"description\" content=\"Learn what the Excel SUBTOTAL function does. Clear syntax explanation and useful formula examples.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/functiontranslator.com\/en\/subtotal\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excel SUBTOTAL Function \u2013 How It Works, Formula Examples and Syntax\" \/>\n<meta property=\"og:description\" content=\"Learn what the Excel SUBTOTAL function does. Clear syntax explanation and useful formula examples.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/functiontranslator.com\/en\/subtotal\/\" \/>\n<meta property=\"og:site_name\" content=\"Function Translator\" \/>\n<meta property=\"article:published_time\" content=\"2025-07-15T13:34:46+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-02-13T11:36:32+00:00\" \/>\n<meta name=\"author\" content=\"glavadmin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:title\" content=\"Excel SUBTOTAL Function \u2013 How It Works, Formula Examples and Syntax\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"glavadmin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/functiontranslator.com\/en\/subtotal\/\",\"url\":\"https:\/\/functiontranslator.com\/en\/subtotal\/\",\"name\":\"Excel SUBTOTAL Function \u2013 How It Works, Formula Examples and Syntax\",\"isPartOf\":{\"@id\":\"https:\/\/functiontranslator.com\/no\/#website\"},\"datePublished\":\"2025-07-15T13:34:46+00:00\",\"dateModified\":\"2021-02-13T11:36:32+00:00\",\"author\":{\"@id\":\"https:\/\/functiontranslator.com\/no\/#\/schema\/person\/87663eece497ca58643166ecb09f1389\"},\"description\":\"Learn what the Excel SUBTOTAL function does. Clear syntax explanation and useful formula examples.\",\"breadcrumb\":{\"@id\":\"https:\/\/functiontranslator.com\/en\/subtotal\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/functiontranslator.com\/en\/subtotal\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/functiontranslator.com\/en\/subtotal\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Function Excel\",\"item\":\"https:\/\/functiontranslator.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Math and trigonometry\",\"item\":\"https:\/\/functiontranslator.com\/en\/math-and-trigonometry\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"SUBTOTAL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/functiontranslator.com\/no\/#website\",\"url\":\"https:\/\/functiontranslator.com\/no\/\",\"name\":\"Function Translator\",\"description\":\"Excel function translator\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/functiontranslator.com\/no\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/functiontranslator.com\/no\/#\/schema\/person\/87663eece497ca58643166ecb09f1389\",\"name\":\"glavadmin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/functiontranslator.com\/no\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/03ccd9347c5339db75deede5a8c5b2bf1d40e0bce26a5251fcaac6331d381db8?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/03ccd9347c5339db75deede5a8c5b2bf1d40e0bce26a5251fcaac6331d381db8?s=96&d=mm&r=g\",\"caption\":\"glavadmin\"},\"sameAs\":[\"https:\/\/functiontranslator.com\"],\"url\":\"https:\/\/functiontranslator.com\/en\/author\/glavadmin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Excel SUBTOTAL Function \u2013 How It Works, Formula Examples and Syntax","description":"Learn what the Excel SUBTOTAL function does. Clear syntax explanation and useful formula examples.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/functiontranslator.com\/en\/subtotal\/","og_locale":"en_US","og_type":"article","og_title":"Excel SUBTOTAL Function \u2013 How It Works, Formula Examples and Syntax","og_description":"Learn what the Excel SUBTOTAL function does. Clear syntax explanation and useful formula examples.","og_url":"https:\/\/functiontranslator.com\/en\/subtotal\/","og_site_name":"Function Translator","article_published_time":"2025-07-15T13:34:46+00:00","article_modified_time":"2021-02-13T11:36:32+00:00","author":"glavadmin","twitter_card":"summary_large_image","twitter_title":"Excel SUBTOTAL Function \u2013 How It Works, Formula Examples and Syntax","twitter_misc":{"Written by":"glavadmin","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/functiontranslator.com\/en\/subtotal\/","url":"https:\/\/functiontranslator.com\/en\/subtotal\/","name":"Excel SUBTOTAL Function \u2013 How It Works, Formula Examples and Syntax","isPartOf":{"@id":"https:\/\/functiontranslator.com\/no\/#website"},"datePublished":"2025-07-15T13:34:46+00:00","dateModified":"2021-02-13T11:36:32+00:00","author":{"@id":"https:\/\/functiontranslator.com\/no\/#\/schema\/person\/87663eece497ca58643166ecb09f1389"},"description":"Learn what the Excel SUBTOTAL function does. Clear syntax explanation and useful formula examples.","breadcrumb":{"@id":"https:\/\/functiontranslator.com\/en\/subtotal\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/functiontranslator.com\/en\/subtotal\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/functiontranslator.com\/en\/subtotal\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Function Excel","item":"https:\/\/functiontranslator.com\/en\/"},{"@type":"ListItem","position":2,"name":"Math and trigonometry","item":"https:\/\/functiontranslator.com\/en\/math-and-trigonometry\/"},{"@type":"ListItem","position":3,"name":"SUBTOTAL"}]},{"@type":"WebSite","@id":"https:\/\/functiontranslator.com\/no\/#website","url":"https:\/\/functiontranslator.com\/no\/","name":"Function Translator","description":"Excel function translator","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/functiontranslator.com\/no\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/functiontranslator.com\/no\/#\/schema\/person\/87663eece497ca58643166ecb09f1389","name":"glavadmin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/functiontranslator.com\/no\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/03ccd9347c5339db75deede5a8c5b2bf1d40e0bce26a5251fcaac6331d381db8?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/03ccd9347c5339db75deede5a8c5b2bf1d40e0bce26a5251fcaac6331d381db8?s=96&d=mm&r=g","caption":"glavadmin"},"sameAs":["https:\/\/functiontranslator.com"],"url":"https:\/\/functiontranslator.com\/en\/author\/glavadmin\/"}]}},"_links":{"self":[{"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/posts\/431","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/comments?post=431"}],"version-history":[{"count":3,"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/posts\/431\/revisions"}],"predecessor-version":[{"id":3322,"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/posts\/431\/revisions\/3322"}],"wp:attachment":[{"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/media?parent=431"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/categories?post=431"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/tags?post=431"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}