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

Today, we’ll explore the AGGREGATE function, an incredibly versatile tool available in both Microsoft Excel and Google Sheets. Designed to simplify various aggregation operations like AVERAGE, COUNT, MAX, MIN, and SUM, this function is especially useful for its ability to bypass errors and hidden rows in data calculations.<\/p>\n

Basics of the AGGREGATE Function:<\/h2>\n

The AGGREGATE function allows you to perform different types of aggregate calculations. It relies on the function_num<\/i> argument to determine the specific operation to conduct\u2014ranging from averaging to summing up values. Crucially, this function also lets you selectively ignore hidden rows, errors, or other specific types of data that might skew your results.<\/p>\n

How to Use the AGGREGATE Function:<\/h2>\n

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

=AGGREGATE(function_num, options, ref1, [ref2], \u2026)<\/code><\/pre>\n
    \n
  • function_num:<\/b> A numeric code that selects the desired aggregation function (e.g., 1 for AVERAGE, 4 for MAX, 6 for MEDIAN, etc.).<\/li>\n
  • options:<\/b> A parameter to specify data to exclude from calculations, such as 0 for including all data, 6 for excluding hidden rows, or 7 for excluding both hidden rows and error values.<\/li>\n
  • ref1, ref2, …:<\/b> These are references to the data ranges or arrays to be included in your calculation.<\/li>\n<\/ul>\n
    Examples of the AGGREGATE Function:<\/h2>\n
    Below are some practical applications of the AGGREGATE function to illustrate its adaptability:<\/p>\n\n\n\n\n\n
    Scenario<\/th>\nExcel Formula<\/th>\nGoogle Sheets Formula<\/th>\n<\/tr>\n
    Calculate the average of visible cells only<\/td>\n=AGGREGATE(1, 5, A1:A10)<\/code><\/td>\n=AGGREGATE(1, 5, A1:A10)<\/code><\/td>\n<\/tr>\n
    Find the maximum value ignoring errors<\/td>\n=AGGREGATE(4, 6, B1:B10)<\/code><\/td>\n=AGGREGATE(4, 6, B1:B10)<\/code><\/td>\n<\/tr>\n
    Count the number of visible cells containing numbers<\/td>\n=AGGREGATE(3, 5, C1:C10)<\/code><\/td>\n=AGGREGATE(3, 5, C1:C10)<\/code><\/td>\n<\/tr>\n<\/table>\n
    These examples demonstrate how the AGGREGATE function can be customized to meet specific data analysis needs in both Excel and Google Sheets, providing a high degree of flexibility in handling diverse datasets.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Returns an aggregate 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-28","post","type-post","status-publish","format-standard","hentry","category-math-and-trigonometry"],"acf":[],"yoast_head":"\nExcel AGGREGATE Function \u2013 How It Works, Formula Examples and Syntax<\/title>\n<meta name=\"description\" content=\"Learn what the Excel AGGREGATE 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\/aggregate\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excel AGGREGATE Function \u2013 How It Works, Formula Examples and Syntax\" \/>\n<meta property=\"og:description\" content=\"Learn what the Excel AGGREGATE function does. Clear syntax explanation and useful formula examples.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/functiontranslator.com\/en\/aggregate\/\" \/>\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:32:33+00:00\" \/>\n<meta name=\"author\" content=\"glavadmin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:title\" content=\"Excel AGGREGATE 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\/aggregate\/\",\"url\":\"https:\/\/functiontranslator.com\/en\/aggregate\/\",\"name\":\"Excel AGGREGATE 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:32:33+00:00\",\"author\":{\"@id\":\"https:\/\/functiontranslator.com\/no\/#\/schema\/person\/87663eece497ca58643166ecb09f1389\"},\"description\":\"Learn what the Excel AGGREGATE function does. Clear syntax explanation and useful formula examples.\",\"breadcrumb\":{\"@id\":\"https:\/\/functiontranslator.com\/en\/aggregate\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/functiontranslator.com\/en\/aggregate\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/functiontranslator.com\/en\/aggregate\/#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\":\"AGGREGATE\"}]},{\"@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 AGGREGATE Function \u2013 How It Works, Formula Examples and Syntax","description":"Learn what the Excel AGGREGATE 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\/aggregate\/","og_locale":"en_US","og_type":"article","og_title":"Excel AGGREGATE Function \u2013 How It Works, Formula Examples and Syntax","og_description":"Learn what the Excel AGGREGATE function does. Clear syntax explanation and useful formula examples.","og_url":"https:\/\/functiontranslator.com\/en\/aggregate\/","og_site_name":"Function Translator","article_published_time":"2025-07-15T13:34:46+00:00","article_modified_time":"2021-02-13T11:32:33+00:00","author":"glavadmin","twitter_card":"summary_large_image","twitter_title":"Excel AGGREGATE 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\/aggregate\/","url":"https:\/\/functiontranslator.com\/en\/aggregate\/","name":"Excel AGGREGATE 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:32:33+00:00","author":{"@id":"https:\/\/functiontranslator.com\/no\/#\/schema\/person\/87663eece497ca58643166ecb09f1389"},"description":"Learn what the Excel AGGREGATE function does. Clear syntax explanation and useful formula examples.","breadcrumb":{"@id":"https:\/\/functiontranslator.com\/en\/aggregate\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/functiontranslator.com\/en\/aggregate\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/functiontranslator.com\/en\/aggregate\/#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":"AGGREGATE"}]},{"@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\/28","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=28"}],"version-history":[{"count":3,"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/posts\/28\/revisions"}],"predecessor-version":[{"id":2919,"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/posts\/28\/revisions\/2919"}],"wp:attachment":[{"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/media?parent=28"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/categories?post=28"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/functiontranslator.com\/en\/wp-json\/wp\/v2\/tags?post=28"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}