Oops! Something went wrong while submitting the form.
.svg)
On its own, the SUBTOTAL function is a quiet powerhouse. In both Excel and Google Sheets it lets you calculate sums, averages, counts, and more that automatically respond to filters and hidden rows. Instead of rewriting SUM or AVERAGE every time you slice your data, you call SUBTOTAL with a function number, like 9 or 109 for SUM, and a range such as A2:A500. Excel even layers this into its Subtotal command so you can insert group-level totals and a grand total in a few clicks.But in a real business, you do not run SUBTOTAL once. You repeat the same ritual every week: export CRM data, paste it into Excel, clean the columns, set up filters, add SUBTOTAL formulas, double check that hidden rows are treated correctly, and then copy charts into a slide deck. That is where an AI computer agent changes the story. Instead of humans re-building the same filtered views and subtotals, you hand the workflow to an agent that opens the sheet, refreshes data, applies the right SUBTOTAL function numbers, validates results against spot checks, and ships your reports while you are on sales calls. Delegating this to an agent turns a fragile manual habit into a dependable background process.
### 1. Manual ways to use SUBTOTAL in Excel and Google SheetsBefore you automate, you need to be confident with the basics. SUBTOTAL works the same conceptually in both tools:- Syntax: SUBTOTAL(function_num, ref1, [ref2], ...)- function_num chooses the operation and how hidden rows behave. - 1–11 include manually hidden rows - 101–111 ignore manually hidden rowsOfficial references:- Excel SUBTOTAL: https://support.microsoft.com/en-us/office/subtotal-function-7b027003-f060-4ade-9040-e478765b9939- Insert Subtotals in Excel lists: https://support.microsoft.com/en-us/office/insert-subtotals-in-a-list-of-data-in-a-worksheet-7881d256-b4fa-4f81-b71e-b0a3d4a52b3a- Google Sheets SUBTOTAL: https://support.google.com/docs/answer/3093649?hl=en#### 1.1 Simple filtered total in Excel1) Put your data in a clean column, say Sales in B2:B500.2) Click any cell inside the data and apply a filter via Data > Filter.3) In a summary cell, enter: - =SUBTOTAL(109, B2:B500) - 109 means SUM while ignoring manually hidden rows.4) Filter your list by region, owner, or product. The SUBTOTAL result will update to include only visible rows.#### 1.2 Grouped subtotals with the Excel Subtotal command1) Sort your data by the grouping column, for example Region.2) Go to Data > Subtotal.3) In At each change in, choose Region.4) In Use function, choose Sum (or Average, Count, etc.).5) In Add subtotal to, tick Sales.6) Click OK. Excel inserts subtotal rows and a grand total using SUBTOTAL formulas automatically.7) Use the outline buttons on the left (1, 2, 3) to collapse or expand detail.#### 1.3 Manual SUBTOTAL in Google Sheets with filters1) Lay out your data, for example Amount in C2:C1000.2) Select the header row and choose Data > Create a filter.3) In a summary cell, enter: - =SUBTOTAL(109, C2:C1000)4) Filter by campaign, channel, or owner. SUBTOTAL recalculates to include only visible rows.#### 1.4 Using different function numbers for analysisIn both Excel and Sheets you can swap function_num for different views:- =SUBTOTAL(101, C2:C1000) for AVERAGE of visible rows- =SUBTOTAL(102, A2:A1000) for COUNT of visible numeric entries- =SUBTOTAL(104, C2:C1000) to get MAX of visible valuesThis lets a marketer or agency owner answer questions like: what is the average deal size of visible deals, or the maximum spend in the filtered period, without touching the underlying data.#### 1.5 Avoiding double countingSUBTOTAL ignores other SUBTOTAL results inside its refs. That means you can safely nest group subtotals and a grand total without inflating your numbers.### 2. No-code automation methodsOnce your formulas are solid, you can reduce repetitive clicks using built-in or low-code tools.#### 2.1 Excel Tables with Total Row1) Select your range and press Ctrl+T to create an Excel Table.2) Check My table has headers and click OK.3) With the table selected, enable the Total Row from Table Design > Total Row.4) In the Total row cell under Sales, pick Sum from the dropdown. Excel inserts SUBTOTAL(109, [Sales]) behind the scenes.5) Whenever you filter the table, the total automatically reflects visible rows only.Pros: Zero formula writing once set up, robust for non-technical users.Cons: Less flexible for multi-sheet or cross-file reporting.#### 2.2 Google Sheets filter views plus SUBTOTAL1) Set up your master sheet with a SUBTOTAL cell, for example: - =SUBTOTAL(109, C2:C10000)2) Use Data > Filter views > Create new filter view.3) Configure different filter views, such as By owner, High value deals, or Q4 pipeline.4) Save each view with a descriptive name. The same SUBTOTAL formula serves all of them.Pros: Non-destructive filters, perfect for teams sharing the same sheet.Cons: Still requires you to open the sheet, switch views, and export data manually.#### 2.3 No-code integrations (Zapier, Make, Power Automate)You can have tools like Zapier or Microsoft Power Automate push fresh data into your Sheets or Excel files where SUBTOTAL is already configured:- Example for Google Sheets and Zapier: 1) Trigger: New deal in your CRM. 2) Action: Append a row into your Google Sheet deals log. 3) Your existing SUBTOTAL cells update automatically for dashboards.- Example for Excel and Power Automate: 1) Trigger: New row in an Excel file stored in OneDrive or SharePoint. 2) Action: Post a summary of the updated SUBTOTAL to Teams or email.Pros: Keeps data live without manual copy paste.Cons: Integrations require setup, and logic still lives in scattered spreadsheets.### 3. Scaling with AI agents (Simular-style workflows)When your team is running dozens of reports across clients or business units, manual and no-code methods still leave you clicking. An AI computer agent, like one built on Simular Pro, can operate your desktop, browser, Excel, and Google Sheets as if it were a trained analyst.#### 3.1 Agent-driven reporting ritualImagine your Monday pipeline ritual:1) The agent opens your CRM, exports data, and saves it.2) It launches Excel, loads the correct workbook, and pastes or imports new rows into the right sheet.3) It verifies that SUBTOTAL formulas exist in key summary cells, and if missing, it writes the right function, such as =SUBTOTAL(109, SalesColumnRange).4) It applies or updates filters, checks totals against last week, and flags anomalies.5) Finally, it exports PDFs or PowerPoint screenshots and drops them into a shared drive or emails them to your team.Pros: Fully hands-off, consistent, and transparent; every agent action can be inspected and adjusted.Cons: Requires initial design and testing of the workflow, and runs best on stable report formats.#### 3.2 Multi-client agency dashboards at scaleFor agencies juggling many client workbooks:1) Store a standard Google Sheets or Excel template where all SUBTOTAL logic is baked in.2) The AI agent duplicates the template per client, renames files, and connects them to each client data source (CSV, exports, APIs via browser).3) On a schedule, the agent refreshes data, lets formulas recalc, checks key metrics against thresholds, and posts client-ready summaries.Pros: Massive leverage for account managers; easy to roll out new clients.Cons: Template changes must be coordinated so the agent scripts stay in sync.#### 3.3 Hybrid: human plus agent collaborationYou might prefer to keep humans in the loop:1) The agent prepares the data and SUBTOTAL-based views.2) A sales leader quickly reviews numbers, adjusts filters, or scenarios.3) The agent then handles publishing, archiving, and notifying stakeholders.Pros: Best of both worlds: human judgment with machine execution.Cons: Still requires lightweight review time from your team.As your reporting load grows, these AI agent patterns turn SUBTOTAL from a handy formula into a backbone of reliable, automated insight delivery across Excel and Google Sheets.