{"id":23823,"date":"2025-07-31T12:35:46","date_gmt":"2025-07-31T07:05:46","guid":{"rendered":"https:\/\/www.pickl.ai\/blog\/?p=23823"},"modified":"2025-07-31T12:35:47","modified_gmt":"2025-07-31T07:05:47","slug":"subtotal-formula-in-excel","status":"publish","type":"post","link":"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/","title":{"rendered":"SUBTOTAL Formula in Excel: Smarter Summaries Made Simple"},"content":{"rendered":"\n<p><strong>Summary: <\/strong>Master the SUBTOTAL formula in Excel for smarter data analysis. This guide explains why it&#8217;s better than SUM for filtered lists, detailing its syntax and function numbers. Learn practical examples for accurate reporting and how to avoid common pitfalls like double-counting, ensuring your summaries are always correct.<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Introduction\" >Introduction<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Why_Use_SUBTOTAL_Instead_of_SUM\" >Why Use SUBTOTAL Instead of SUM?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Basic_Syntax_of_the_SUBTOTAL_Function\" >Basic Syntax of the SUBTOTAL Function<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Difference_Between_SUBTOTAL_and_SUM\" >Difference Between SUBTOTAL and SUM<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Most_Common_Uses_of_SUBTOTAL\" >Most Common Uses of SUBTOTAL<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Analyzing_Filtered_Data\" >Analyzing Filtered Data<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Creating_Reports_with_Subtotals_and_Grand_Totals\" >Creating Reports with Subtotals and Grand Totals<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Working_with_Excel_Tables\" >Working with Excel Tables<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Building_Interactive_Dashboards\" >Building Interactive Dashboards<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Function_Numbers_for_SUBTOTAL\" >Function Numbers for SUBTOTAL<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Practical_Examples_of_SUBTOTAL_in_Excel\" >Practical Examples of SUBTOTAL in Excel<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Example_1_Summing_Filtered_Data\" >Example 1: Summing Filtered Data<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Example_2_Counting_Visible_Rows\" >Example 2: Counting Visible Rows<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-14\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Example_3_Excluding_Manually_Hidden_Rows\" >Example 3: Excluding Manually Hidden Rows<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-15\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Limitations_of_SUBTOTAL\" >Limitations of SUBTOTAL<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-16\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Horizontal_Ranges\" >Horizontal Ranges<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-17\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#3D_References\" >3D References<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-18\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Array_Formulas\" >Array Formulas<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-19\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Error_Values\" >Error Values<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-20\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Tips_for_Using_SUBTOTAL_Effectively\" >Tips for Using SUBTOTAL Effectively<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-21\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Use_Excel_Tables\" >Use Excel Tables<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-22\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Sort_Your_Data_First\" >Sort Your Data First<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-23\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Combine_with_Other_Functions\" >Combine with Other Functions<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-24\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Remember_the_Function_Numbers\" >Remember the Function Numbers<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-25\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Frequently_Asked_Questions\" >Frequently Asked Questions<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-26\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#What_is_the_SUBTOTAL_formula_in_Excel\" >What is the SUBTOTAL formula in Excel?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-27\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#How_does_the_SUBTOTAL_formula_work\" >How does the SUBTOTAL formula work?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-28\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#Why_use_SUBTOTAL_instead_of_SUM_in_Excel\" >Why use SUBTOTAL instead of SUM in Excel?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-29\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#How_do_you_subtotal_filtered_rows_in_Excel\" >How do you subtotal filtered rows in Excel?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-30\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#What_is_the_difference_between_SUBTOTAL_9_and_109\" >What is the difference between SUBTOTAL 9 and 109?<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-31\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#In_conclusion\" >In conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2 id=\"introduction\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Introduction\"><\/span><strong>Introduction<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>In the world of Excel, calculating totals is a fundamental task. While the SUM function is often the go-to for this, a more powerful and versatile tool lies in the SUBTOTAL formula in Excel. This dynamic function offers a smarter way to summarize your data, especially when dealing with filtered or hidden rows.<\/p>\n\n\n\n<p>If you have ever found yourself frustrated with incorrect totals in a filtered list, the SUBTOTAL function is the solution you&#8217;ve been looking for.<\/p>\n\n\n\n<p><strong>Key Takeaways<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use SUBTOTAL for accurate calculations on filtered or hidden data.<\/li>\n\n\n\n<li>The formula smartly ignores other subtotals, preventing double-counting errors.<\/li>\n\n\n\n<li>Function 9 sums filtered data; 109 also excludes manually hidden rows.<\/li>\n\n\n\n<li>SUBTOTAL is far more versatile and dynamic than the basic SUM.<\/li>\n\n\n\n<li>It&#8217;s the best way to create accurate grand totals for reports.<\/li>\n<\/ul>\n\n\n\n<h2 id=\"why-use-subtotal-instead-of-sum\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Why_Use_SUBTOTAL_Instead_of_SUM\"><\/span><strong>Why Use SUBTOTAL Instead of SUM?<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/lh7-rt.googleusercontent.com\/docsz\/AD_4nXeUt_it9gpPHjvl2HNPczv4UJslYQEBT1yB2Hx49I0QaRJVaKMhW4KRpI79Nwowl_tnFlSPiHpS0QCKJSRwDDDlCbE1nbrR19G-SMZBjyTXD42zxrgvi0bUggXVlxpguxcIph-L?key=P9oRuIEdapKt9T-je_TvCQ\" alt=\"SUBTOTAL Function Advantage\"\/><\/figure>\n\n\n\n<p>The primary advantage of the SUBTOTAL formula in Excel is its ability to work with visible cells only. When you filter a dataset, the SUM function continues to include all values in its calculation, including those in the hidden rows, which can lead to inaccurate results.&nbsp;<\/p>\n\n\n\n<p>In contrast, the SUBTOTAL function intelligently recalculates to only include the data that is currently visible after a filter is applied.<\/p>\n\n\n\n<p>Furthermore, SUBTOTAL is designed to ignore other SUBTOTAL formulas within its range, preventing the common error of double-counting when you have subtotals and a grand total in the same column. This makes it an indispensable tool for creating accurate and dynamic reports.<\/p>\n\n\n\n<h2 id=\"basic-syntax-of-the-subtotal-function\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Basic_Syntax_of_the_SUBTOTAL_Function\"><\/span><strong>Basic Syntax of the SUBTOTAL Function<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The syntax for the SUBTOTAL function is straightforward:<\/p>\n\n\n\n<p>=SUBTOTAL(function_num, ref1, [ref2], &#8230;)<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>function_num:<\/strong> This is a required numerical argument that specifies which function to use for the subtotal (e.g., SUM, AVERAGE, COUNT).<\/li>\n\n\n\n<li><strong>ref1:<\/strong> This is the first required range of cells you want to subtotal.<\/li>\n\n\n\n<li><strong>[ref2], &#8230;:<\/strong> These are optional additional ranges of cells you want to include in your calculation, up to 254 ranges<\/li>\n<\/ul>\n\n\n\n<h2 id=\"difference-between-subtotal-and-sum\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Difference_Between_SUBTOTAL_and_SUM\"><\/span><strong>Difference Between SUBTOTAL and SUM<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The core difference lies in their handling of filtered and hidden data. Here\u2019s a quick comparison:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><strong>Feature<\/strong><\/td><td><strong>SUM<\/strong><\/td><td><strong>SUBTOTAL<\/strong><\/td><\/tr><tr><td><strong>Filtered Rows<\/strong><\/td><td>Includes all rows, visible or not.<\/td><td>Ignores filtered (hidden) rows by default.<\/td><\/tr><tr><td><strong>Manually Hidden Rows<\/strong><\/td><td>Includes manually hidden rows.<\/td><td>Can be configured to include or exclude manually hidden rows.<\/td><\/tr><tr><td><strong>Nested Subtotals<\/strong><\/td><td>Includes other SUM or SUBTOTAL formulas in its range.<\/td><td>Ignores other SUBTOTAL formulas to prevent double-counting<\/td><\/tr><tr><td><strong>Functionality<\/strong><\/td><td>Only performs addition.<\/td><td>Can perform various calculations like SUM, AVERAGE, COUNT, MAX, MIN, etc.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>In essence, SUM is for a straightforward, static total, while SUBTOTAL provides a dynamic and adaptable summary that responds to changes in your data&#8217;s visibility.<\/p>\n\n\n\n<h2 id=\"most-common-uses-of-subtotal\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Most_Common_Uses_of_SUBTOTAL\"><\/span><strong>Most Common Uses of SUBTOTAL<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Discover how the SUBTOTAL function enhances Excel data analysis with its versatile uses. This section explores the most common applications of SUBTOTAL, including dynamic summaries for filtered data, avoiding double counting, and flexible aggregation, empowering you to create accurate, efficient, and adaptable reports effortlessly.&nbsp;<\/p>\n\n\n\n<h3 id=\"analyzing-filtered-data\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Analyzing_Filtered_Data\"><\/span><strong>Analyzing Filtered Data<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>This is the most common and powerful use. When you apply filters to your data, SUBTOTAL ensures your summary calculations (like total sales for a specific region) are always accurate for the visible data.<\/p>\n\n\n\n<h3 id=\"creating-reports-with-subtotals-and-grand-totals\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Creating_Reports_with_Subtotals_and_Grand_Totals\"><\/span><strong>Creating Reports with Subtotals and Grand Totals<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>When you have a list with subtotals for different categories and a grand total at the end, using SUBTOTAL for all calculations prevents the grand total from incorrectly adding up the subtotal values.<\/p>\n\n\n\n<h3 id=\"working-with-excel-tables\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Working_with_Excel_Tables\"><\/span><strong>Working with Excel Tables<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Excel&#8217;s table feature often uses the SUBTOTAL function in its total row, allowing for dynamic calculations as you filter the table data.<\/p>\n\n\n\n<h3 id=\"building-interactive-dashboards\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Building_Interactive_Dashboards\"><\/span><strong>Building Interactive Dashboards<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>SUBTOTAL is excellent for creating interactive dashboards where users can filter data and see the summary metrics update in real-time.<\/p>\n\n\n\n<h2 id=\"function-numbers-for-subtotal\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Function_Numbers_for_SUBTOTAL\"><\/span><strong>Function Numbers for SUBTOTAL<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The function_num argument is what gives SUBTOTAL its power. There are two sets of function numbers you can use:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><strong>Function<\/strong><\/td><td><strong>Include Hidden Rows (1-11)<\/strong><\/td><td><strong>Exclude Hidden Rows (101-111)<\/strong><\/td><\/tr><tr><td>AVERAGE<\/td><td>1<\/td><td>101<\/td><\/tr><tr><td>COUNT<\/td><td>2<\/td><td>102<\/td><\/tr><tr><td>COUNTA<\/td><td>3<\/td><td>103<\/td><\/tr><tr><td>MAX<\/td><td>4<\/td><td>104<\/td><\/tr><tr><td>MIN<\/td><td>5<\/td><td>105<\/td><\/tr><tr><td>PRODUCT<\/td><td>6<\/td><td>106<\/td><\/tr><tr><td>STDEV<\/td><td>7<\/td><td>107<\/td><\/tr><tr><td>STDEVP<\/td><td>8<\/td><td>108<\/td><\/tr><tr><td>SUM<\/td><td>9<\/td><td>109<\/td><\/tr><tr><td>VAR<\/td><td>10<\/td><td>110<\/td><\/tr><tr><td>VARP<\/td><td>11<\/td><td>111<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>The key difference between the two sets is how they handle rows that you have manually hidden (by right-clicking and selecting &#8220;Hide&#8221;).Both sets will always exclude rows hidden by a filter.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>1-11:<\/strong> Includes values in manually hidden rows.<\/li>\n\n\n\n<li><strong>101-111:<\/strong> Excludes values in manually hidden rows, providing a true &#8220;what you see is what you get&#8221; calculation.<\/li>\n<\/ul>\n\n\n\n<h2 id=\"practical-examples-of-subtotal-in-excel\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Practical_Examples_of_SUBTOTAL_in_Excel\"><\/span><strong>Practical Examples of SUBTOTAL in Excel<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Let&#8217;s consider a sales data table with columns for &#8216;Region&#8217;, &#8216;Product&#8217;, and &#8216;Sales&#8217;.<\/p>\n\n\n\n<h3 id=\"example-1-summing-filtered-data\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Example_1_Summing_Filtered_Data\"><\/span><strong>Example 1: Summing Filtered Data<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Imagine you want to see the total sales for the &#8220;North&#8221; region.<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Apply a filter to your data range.<\/li>\n\n\n\n<li>In the &#8216;Region&#8217; column filter, select &#8220;North&#8221;.<\/li>\n\n\n\n<li>In a cell where you want the total, enter the formula: =SUBTOTAL(9, C2:C100) (assuming sales data is in C2:C100).<\/li>\n<\/ol>\n\n\n\n<p>The result will be the sum of sales for only the &#8220;North&#8221; region. If you change the filter to &#8220;South&#8221;, the SUBTOTAL will automatically update.<\/p>\n\n\n\n<h3 id=\"example-2-counting-visible-rows\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Example_2_Counting_Visible_Rows\"><\/span><strong>Example 2: Counting Visible Rows<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>To count the number of sales transactions visible after filtering for a specific product:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Apply a filter to the &#8216;Product&#8217; column.<\/li>\n\n\n\n<li>Use the formula: =SUBTOTAL(2, C2:C100)<\/li>\n<\/ol>\n\n\n\n<p>This will give you a count of the visible sales entries.<\/p>\n\n\n\n<h3 id=\"example-3-excluding-manually-hidden-rows\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Example_3_Excluding_Manually_Hidden_Rows\"><\/span><strong>Example 3: Excluding Manually Hidden Rows<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>If you have manually hidden some rows and want to calculate the average of only the visible cells, use the 101-111 series of function numbers.<\/p>\n\n\n\n<p>=SUBTOTAL(101, C2:C100)<\/p>\n\n\n\n<p>This will calculate the average of the visible cells, ignoring both filtered and manually hidden rows.<\/p>\n\n\n\n<h2 id=\"limitations-of-subtotal\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Limitations_of_SUBTOTAL\"><\/span><strong>Limitations of SUBTOTAL<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>While powerful for filtering scenarios, SUBTOTAL has specific constraints related to the scope of its calculations, the functions supported, and behavior with hidden data. For more complex data analysis, complement SUBTOTAL with <a href=\"https:\/\/www.pickl.ai\/blog\/learn-how-to-make-and-use-pivot-table-in-excel\/\">Pivot Tables<\/a> or other functions.<\/p>\n\n\n\n<h3 id=\"horizontal-ranges\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Horizontal_Ranges\"><\/span><strong>Horizontal Ranges<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>The function is designed to work with vertical data (columns). When used on a horizontal range, it will not exclude values in hidden columns.<\/p>\n\n\n\n<h3 id=\"3d-references\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"3D_References\"><\/span><strong>3D References<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>It does not support 3D references, which are references to the same cell or range across multiple worksheets.<\/p>\n\n\n\n<h3 id=\"array-formulas\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Array_Formulas\"><\/span><strong>Array Formulas<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>SUBTOTAL does not work directly within array formulas in the way some other functions do. For more complex scenarios, the <a href=\"https:\/\/www.pickl.ai\/blog\/what-are-sql-aggregate-functions-types-and-importance\/\">AGGREGATE function<\/a> might be a better choice.<\/p>\n\n\n\n<h3 id=\"error-values\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Error_Values\"><\/span><strong>Error Values<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>The SUBTOTAL function can be affected by error values within the specified range.<\/p>\n\n\n\n<h2 id=\"tips-for-using-subtotal-effectively\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Tips_for_Using_SUBTOTAL_Effectively\"><\/span><strong>Tips for Using SUBTOTAL Effectively<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/lh7-rt.googleusercontent.com\/docsz\/AD_4nXdua1XOHgeTlyTxf0buMoz7VX_zZehfSonm8PgaZg09bfDgGO4wpHXorXBJSg2WyoLxZ3WmEDorOZDQLzCzzw2eKBgPkAc9FxOQS3pRZChUsa3yqgaoreTj_yQl5aSG-Va5vJw3?key=P9oRuIEdapKt9T-je_TvCQ\" alt=\"Mastering SUBTOTAL in Excel\"\/><\/figure>\n\n\n\n<p>Unlock the full potential of your Excel reports by mastering the SUBTOTAL function. This section highlights practical tips and best practices for using SUBTOTAL effectively, helping you analyze filtered data, avoid common pitfalls, and create dynamic summaries for smarter and more efficient spreadsheet work.\u00a0<\/p>\n\n\n\n<h3 id=\"use-excel-tables\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Use_Excel_Tables\"><\/span><strong>Use Excel Tables<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Converting your data range to an Excel Table (Ctrl+T) and enabling the &#8220;Total Row&#8221; will automatically insert SUBTOTAL formulas for you.<\/p>\n\n\n\n<h3 id=\"sort-your-data-first\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Sort_Your_Data_First\"><\/span><strong>Sort Your Data First<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>When using the automatic subtotal feature (Data tab &gt; Subtotal), ensure your data is sorted by the column you want to group by.<\/p>\n\n\n\n<h3 id=\"combine-with-other-functions\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Combine_with_Other_Functions\"><\/span><strong>Combine with Other Functions<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>SUBTOTAL can be nested within other functions like IF to create more complex and conditional calculations.<\/p>\n\n\n\n<h3 id=\"remember-the-function-numbers\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Remember_the_Function_Numbers\"><\/span><strong>Remember the Function Numbers<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>While you don&#8217;t need to memorize them all, knowing that 9 is for SUM and 109 is for SUM of visible cells only is a great starting point.<\/p>\n\n\n\n<h2 id=\"frequently-asked-questions\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Frequently_Asked_Questions\"><\/span><strong>Frequently Asked Questions<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<h3 id=\"what-is-the-subtotal-formula-in-excel\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"What_is_the_SUBTOTAL_formula_in_Excel\"><\/span><strong>What is the SUBTOTAL formula in Excel?<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>The SUBTOTAL formula in Excel is a versatile function that calculates a subtotal for a range of cells.It can perform various operations like SUM, AVERAGE, and COUNT, and it has the unique ability to include or exclude hidden and filtered cells from the calculation.<\/p>\n\n\n\n<h3 id=\"how-does-the-subtotal-formula-work\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_does_the_SUBTOTAL_formula_work\"><\/span><strong>How does the SUBTOTAL formula work?<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>The SUBTOTAL formula works by applying a specified mathematical function (indicated by a function number) to a range of cells. Its key feature is that it can dynamically adjust the calculation to only include visible cells when a filter is applied.<\/p>\n\n\n\n<h3 id=\"why-use-subtotal-instead-of-sum-in-excel\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Why_use_SUBTOTAL_instead_of_SUM_in_Excel\"><\/span><strong>Why use SUBTOTAL instead of SUM in Excel?<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>You should use SUBTOTAL instead of SUM when you are working with filtered data or when you have subtotals within your data set. SUBTOTAL will give you accurate results for visible cells only and will ignore other SUBTOTAL formulas, preventing double-counting.<\/p>\n\n\n\n<h3 id=\"how-do-you-subtotal-filtered-rows-in-excel\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_do_you_subtotal_filtered_rows_in_Excel\"><\/span><strong>How do you subtotal filtered rows in Excel?<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>To subtotal filtered rows, use the SUBTOTAL function. For example, to sum a filtered column, you would use the formula =SUBTOTAL(9, range). The function will automatically ignore the rows that are hidden by the filter.<\/p>\n\n\n\n<h3 id=\"what-is-the-difference-between-subtotal-9-and-109\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"What_is_the_difference_between_SUBTOTAL_9_and_109\"><\/span><strong>What is the difference between SUBTOTAL 9 and 109?<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Both SUBTOTAL(9, &#8230;) and SUBTOTAL(109, &#8230;) calculate the sum of a range. The difference is in how they treat manually hidden rows. SUBTOTAL 9 includes manually hidden rows in its calculation, while SUBTOTAL 109 excludes them. Both will exclude rows hidden by a filter.<\/p>\n\n\n\n<h2 id=\"in-conclusion\" class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"In_conclusion\"><\/span><strong>In conclusion<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Mastering the <strong>subtotal formula in Excel<\/strong> is a significant step towards more sophisticated and accurate <a href=\"https:\/\/www.pickl.ai\/blog\/advanced-excel-chart-types\/\">data analysis<\/a>. By understanding its syntax, appreciating its advantages over the standard SUM function, and learning to leverage its various function numbers, you can create more dynamic, reliable, and user-friendly spreadsheets.<\/p>\n\n\n\n<p>While there are some limitations, for the vast majority of summary tasks involving filtered or grouped data, SUBTOTAL is the superior choice.<\/p>\n\n\n\n<p>For those dealing with highly specialized or industry-specific data calculations that may push the boundaries of standard Excel functions, exploring dedicated data analysis tools or industry-specific software add-ins could provide even more tailored solutions.<\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"Learn to use the powerful SUBTOTAL formula for accurate summaries on filtered Excel data.\n","protected":false},"author":4,"featured_media":23824,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[2318],"tags":[4092],"ppma_author":[2169,2633],"class_list":{"0":"post-23823","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-excel","8":"tag-subtotal-formula-in-excel"},"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v20.3 (Yoast SEO v27.3) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>SUBTOTAL Formula in Excel<\/title>\n<meta name=\"description\" content=\"Unlock the power of the SUBTOTAL formula in Excel. Learn to accurately summarize filtered data, avoid common errors, and create reports\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SUBTOTAL Formula in Excel: Smarter Summaries Made Simple\" \/>\n<meta property=\"og:description\" content=\"Unlock the power of the SUBTOTAL formula in Excel. Learn to accurately summarize filtered data, avoid common errors, and create reports\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/\" \/>\n<meta property=\"og:site_name\" content=\"Pickl.AI\" \/>\n<meta property=\"article:published_time\" content=\"2025-07-31T07:05:46+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-07-31T07:05:47+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.pickl.ai\/blog\/wp-content\/uploads\/2025\/07\/image3-8.png\" \/>\n\t<meta property=\"og:image:width\" content=\"800\" \/>\n\t<meta property=\"og:image:height\" content=\"500\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Neha Singh, Jogith Chandran\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Neha Singh\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/subtotal-formula-in-excel\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/subtotal-formula-in-excel\\\/\"},\"author\":{\"name\":\"Neha Singh\",\"@id\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/#\\\/schema\\\/person\\\/2ad633a6bc1b93bc13591b60895be308\"},\"headline\":\"SUBTOTAL Formula in Excel: Smarter Summaries Made Simple\",\"datePublished\":\"2025-07-31T07:05:46+00:00\",\"dateModified\":\"2025-07-31T07:05:47+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/subtotal-formula-in-excel\\\/\"},\"wordCount\":1584,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/subtotal-formula-in-excel\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/wp-content\\\/uploads\\\/2025\\\/07\\\/image3-8.png\",\"keywords\":[\"SUBTOTAL Formula in Excel\"],\"articleSection\":[\"Excel\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/subtotal-formula-in-excel\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/subtotal-formula-in-excel\\\/\",\"url\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/subtotal-formula-in-excel\\\/\",\"name\":\"SUBTOTAL Formula in Excel\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/subtotal-formula-in-excel\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/subtotal-formula-in-excel\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/wp-content\\\/uploads\\\/2025\\\/07\\\/image3-8.png\",\"datePublished\":\"2025-07-31T07:05:46+00:00\",\"dateModified\":\"2025-07-31T07:05:47+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/#\\\/schema\\\/person\\\/2ad633a6bc1b93bc13591b60895be308\"},\"description\":\"Unlock the power of the SUBTOTAL formula in Excel. Learn to accurately summarize filtered data, avoid common errors, and create reports\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/subtotal-formula-in-excel\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/subtotal-formula-in-excel\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/subtotal-formula-in-excel\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/wp-content\\\/uploads\\\/2025\\\/07\\\/image3-8.png\",\"contentUrl\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/wp-content\\\/uploads\\\/2025\\\/07\\\/image3-8.png\",\"width\":800,\"height\":500,\"caption\":\"Mastering EXCEL Tool\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/subtotal-formula-in-excel\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel\",\"item\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/category\\\/excel\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"SUBTOTAL Formula in Excel: Smarter Summaries Made Simple\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/\",\"name\":\"Pickl.AI\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/#\\\/schema\\\/person\\\/2ad633a6bc1b93bc13591b60895be308\",\"name\":\"Neha Singh\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/pickl.ai\\\/blog\\\/wp-content\\\/uploads\\\/2024\\\/06\\\/avatar_user_4_1717572961-96x96.jpg3d1a0d35d7a1a929f4a120e9053cbdb5\",\"url\":\"https:\\\/\\\/pickl.ai\\\/blog\\\/wp-content\\\/uploads\\\/2024\\\/06\\\/avatar_user_4_1717572961-96x96.jpg\",\"contentUrl\":\"https:\\\/\\\/pickl.ai\\\/blog\\\/wp-content\\\/uploads\\\/2024\\\/06\\\/avatar_user_4_1717572961-96x96.jpg\",\"caption\":\"Neha Singh\"},\"description\":\"I\u2019m a full-time freelance writer and editor who enjoys wordsmithing. The 8 years long journey as a content writer and editor has made me relaize the significance and power of choosing the right words. Prior to my writing journey, I was a trainer and human resource manager. WIth more than a decade long professional journey, I find myself more powerful as a wordsmith. As an avid writer, everything around me inspires me and pushes me to string words and ideas to create unique content; and when I\u2019m not writing and editing, I enjoy experimenting with my culinary skills, reading, gardening, and spending time with my adorable little mutt Neel.\",\"url\":\"https:\\\/\\\/www.pickl.ai\\\/blog\\\/author\\\/nehasingh\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SUBTOTAL Formula in Excel","description":"Unlock the power of the SUBTOTAL formula in Excel. Learn to accurately summarize filtered data, avoid common errors, and create reports","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:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/","og_locale":"en_US","og_type":"article","og_title":"SUBTOTAL Formula in Excel: Smarter Summaries Made Simple","og_description":"Unlock the power of the SUBTOTAL formula in Excel. Learn to accurately summarize filtered data, avoid common errors, and create reports","og_url":"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/","og_site_name":"Pickl.AI","article_published_time":"2025-07-31T07:05:46+00:00","article_modified_time":"2025-07-31T07:05:47+00:00","og_image":[{"width":800,"height":500,"url":"https:\/\/www.pickl.ai\/blog\/wp-content\/uploads\/2025\/07\/image3-8.png","type":"image\/png"}],"author":"Neha Singh, Jogith Chandran","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Neha Singh","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#article","isPartOf":{"@id":"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/"},"author":{"name":"Neha Singh","@id":"https:\/\/www.pickl.ai\/blog\/#\/schema\/person\/2ad633a6bc1b93bc13591b60895be308"},"headline":"SUBTOTAL Formula in Excel: Smarter Summaries Made Simple","datePublished":"2025-07-31T07:05:46+00:00","dateModified":"2025-07-31T07:05:47+00:00","mainEntityOfPage":{"@id":"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/"},"wordCount":1584,"commentCount":0,"image":{"@id":"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#primaryimage"},"thumbnailUrl":"https:\/\/www.pickl.ai\/blog\/wp-content\/uploads\/2025\/07\/image3-8.png","keywords":["SUBTOTAL Formula in Excel"],"articleSection":["Excel"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/","url":"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/","name":"SUBTOTAL Formula in Excel","isPartOf":{"@id":"https:\/\/www.pickl.ai\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#primaryimage"},"image":{"@id":"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#primaryimage"},"thumbnailUrl":"https:\/\/www.pickl.ai\/blog\/wp-content\/uploads\/2025\/07\/image3-8.png","datePublished":"2025-07-31T07:05:46+00:00","dateModified":"2025-07-31T07:05:47+00:00","author":{"@id":"https:\/\/www.pickl.ai\/blog\/#\/schema\/person\/2ad633a6bc1b93bc13591b60895be308"},"description":"Unlock the power of the SUBTOTAL formula in Excel. Learn to accurately summarize filtered data, avoid common errors, and create reports","breadcrumb":{"@id":"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#primaryimage","url":"https:\/\/www.pickl.ai\/blog\/wp-content\/uploads\/2025\/07\/image3-8.png","contentUrl":"https:\/\/www.pickl.ai\/blog\/wp-content\/uploads\/2025\/07\/image3-8.png","width":800,"height":500,"caption":"Mastering EXCEL Tool"},{"@type":"BreadcrumbList","@id":"https:\/\/www.pickl.ai\/blog\/subtotal-formula-in-excel\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.pickl.ai\/blog\/"},{"@type":"ListItem","position":2,"name":"Excel","item":"https:\/\/www.pickl.ai\/blog\/category\/excel\/"},{"@type":"ListItem","position":3,"name":"SUBTOTAL Formula in Excel: Smarter Summaries Made Simple"}]},{"@type":"WebSite","@id":"https:\/\/www.pickl.ai\/blog\/#website","url":"https:\/\/www.pickl.ai\/blog\/","name":"Pickl.AI","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.pickl.ai\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.pickl.ai\/blog\/#\/schema\/person\/2ad633a6bc1b93bc13591b60895be308","name":"Neha Singh","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/pickl.ai\/blog\/wp-content\/uploads\/2024\/06\/avatar_user_4_1717572961-96x96.jpg3d1a0d35d7a1a929f4a120e9053cbdb5","url":"https:\/\/pickl.ai\/blog\/wp-content\/uploads\/2024\/06\/avatar_user_4_1717572961-96x96.jpg","contentUrl":"https:\/\/pickl.ai\/blog\/wp-content\/uploads\/2024\/06\/avatar_user_4_1717572961-96x96.jpg","caption":"Neha Singh"},"description":"I\u2019m a full-time freelance writer and editor who enjoys wordsmithing. The 8 years long journey as a content writer and editor has made me relaize the significance and power of choosing the right words. Prior to my writing journey, I was a trainer and human resource manager. WIth more than a decade long professional journey, I find myself more powerful as a wordsmith. As an avid writer, everything around me inspires me and pushes me to string words and ideas to create unique content; and when I\u2019m not writing and editing, I enjoy experimenting with my culinary skills, reading, gardening, and spending time with my adorable little mutt Neel.","url":"https:\/\/www.pickl.ai\/blog\/author\/nehasingh\/"}]}},"jetpack_featured_media_url":"https:\/\/www.pickl.ai\/blog\/wp-content\/uploads\/2025\/07\/image3-8.png","authors":[{"term_id":2169,"user_id":4,"is_guest":0,"slug":"nehasingh","display_name":"Neha Singh","avatar_url":"https:\/\/pickl.ai\/blog\/wp-content\/uploads\/2024\/06\/avatar_user_4_1717572961-96x96.jpg","first_name":"Neha","user_url":"","last_name":"Singh","description":"I\u2019m a full-time freelance writer and editor who enjoys wordsmithing. The 8 years long journey as a content writer and editor has made me relaize the significance and power of choosing the right words. Prior to my writing journey, I was a trainer and human resource manager. WIth more than a decade long professional journey, I find myself more powerful as a wordsmith. As an avid writer, everything around me inspires me and pushes me to string words and ideas to create unique content; and when I\u2019m not writing and editing, I enjoy experimenting with my culinary skills, reading, gardening, and spending time with my adorable little mutt Neel."},{"term_id":2633,"user_id":46,"is_guest":0,"slug":"jogithschandran","display_name":"Jogith Chandran","avatar_url":"https:\/\/pickl.ai\/blog\/wp-content\/uploads\/2024\/07\/avatar_user_46_1722419766-96x96.jpg","first_name":"Jogith","user_url":"","last_name":"Chandran","description":"Jogith S Chandran has joined our organization as an Analyst in Gurgaon. He completed his Bachelors IIIT Delhi in CSE this summer. He is interested in NLP, Reinforcement Learning, and AI Safety. He has hobbies like Photography and playing the Saxophone."}],"_links":{"self":[{"href":"https:\/\/www.pickl.ai\/blog\/wp-json\/wp\/v2\/posts\/23823","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pickl.ai\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.pickl.ai\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.pickl.ai\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.pickl.ai\/blog\/wp-json\/wp\/v2\/comments?post=23823"}],"version-history":[{"count":2,"href":"https:\/\/www.pickl.ai\/blog\/wp-json\/wp\/v2\/posts\/23823\/revisions"}],"predecessor-version":[{"id":23827,"href":"https:\/\/www.pickl.ai\/blog\/wp-json\/wp\/v2\/posts\/23823\/revisions\/23827"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.pickl.ai\/blog\/wp-json\/wp\/v2\/media\/23824"}],"wp:attachment":[{"href":"https:\/\/www.pickl.ai\/blog\/wp-json\/wp\/v2\/media?parent=23823"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.pickl.ai\/blog\/wp-json\/wp\/v2\/categories?post=23823"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.pickl.ai\/blog\/wp-json\/wp\/v2\/tags?post=23823"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.pickl.ai\/blog\/wp-json\/wp\/v2\/ppma_author?post=23823"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}