Introduction
A stock planner filters a worksheet to one vending location. The displayed total is used to approve the next replenishment order. Another reviewer manually hides a disputed row, expecting it to leave the calculation. Whether that happens depends on the formula’s first argument. This is a hypothetical purchasing example, not a report from a customer deployment.
A number labelled Total does not identify its population. It might represent the filter result including manually hidden records, or only records that remain visible after both operations. Both are understandable review choices, but they answer different questions. A procurement decision needs the choice written down beside the number.
The Microsoft SUBTOTAL function reference, reviewed on 11 October 2026, documents this distinction. This article applies that narrow Excel behaviour to a stock-review brief. It does not claim that any listed vending machine exports an Excel workbook, uses this formula or has passed a spreadsheet acceptance test.
Quick Answer
For a vertical quantity range, =SUBTOTAL(9,D2:D5) sums the filter result and includes manually hidden rows within it. =SUBTOTAL(109,D2:D5) sums the filter result while excluding manually hidden rows. Microsoft lists both 9 and 109 as SUM operations. Filtered-out rows are excluded regardless of the function number.
Choose the formula according to the agreed population, not because 109 looks more complete or because 9 is shorter. If a disputed record remains part of the approved stock population, hiding it for presentation should not silently remove its units. If the review explicitly concerns visible records only, excluding that hidden row may be appropriate.
Retain the range, quantity unit, filter criteria, manual-hidden-row policy and approved result. A correct subtotal does not prove that stock records are current, duplicates are resolved or every row belongs to the same physical inventory. Those are separate evidence questions.
Comparison Table
This table compares documented calculation behaviour for vertical data ranges. It is not a performance ranking of equipment or a universal description of other spreadsheet applications.
| Review state |
SUBTOTAL with 9 |
SUBTOTAL with 109 |
Decision to record |
| Row included by filter and visible |
Quantity included |
Quantity included |
The record belongs to this review. |
| Row excluded by the filter |
Quantity excluded |
Quantity excluded |
Name the actual filter criteria. |
| Row included by filter but manually hidden |
Quantity included |
Quantity excluded |
Decide whether presentation hiding changes scope. |
| Nested SUBTOTAL inside the references |
Nested subtotal ignored |
Nested subtotal ignored |
Do not generalise this to arbitrary total cells. |
| Horizontal range with a hidden column |
Not the intended vertical-row workflow |
Hiding a column does not change the subtotal |
Do not transpose the hidden-row assumption. |
Who Should Buy This
This brief is useful for buyers who review stock quantities in Excel before ordering cabinets, planning replenishment or evaluating an offered inventory workflow. It also helps an operator receiving a supplier-prepared worksheet whose total changes when records are hidden. Start by confirming that this worksheet is actually part of the proposed delivery.
The stock owner should define which records count. The worksheet author should implement the appropriate function number and reference range. The reviewer should approve the resulting population and preserve the review state. Assigning all three responsibilities to a cell labelled Total leaves the business rule implicit.
A buyer comparing hardware without a worksheet requirement can still use the product shortlist below. The spreadsheet criterion becomes relevant only when a supplier or another platform provider offers that reporting deliverable. Do not convert an inventory-management listing into a promise of a particular Excel formula.
How We Evaluate Smart Vending Machines
Our equipment comparison is a purchasing shortlist based on public manufacturer listings. It is not independent testing. The following is a proposed worksheet evaluation using harmless invented quantities; no actual operator stock, sales or replenishment order has been inspected.
Create a four-record vertical quantity column with values 10, 20, 30 and 40. Before hiding or filtering anything, both SUM forms of SUBTOTAL have an expected result of 100. Record the exact cells and make sure these are numeric unit quantities rather than values with different units or summary rows.
Next filter out the record carrying 40. The expected total is 60 with either 9 or 109. Then manually hide the included row carrying 20. The expected result remains 60 with 9 and becomes 40 with 109. These numbers are an illustrative calculation derived from the documented rules, not a claimed execution in Excel or a vending platform.
Restore the review state deliberately and check that the result follows the chosen rule. Have a second reviewer identify the selected records without relying on the total alone. Retain the workbook version and a readable evidence view that shows the formula, reference range and criteria. A screenshot of 40 alone cannot distinguish this case from a different set of records that also adds to 40.
If the delivered workbook contains nested SUBTOTAL formulas, examine that structure separately. Microsoft says nested subtotals in the references are ignored to avoid double counting. That does not mean every manually typed group total or every other aggregate formula is automatically ignored. Require the author to explain which cells are detail and which are summaries.
Key Buying Factors
Population before presentation. Decide whether hiding a row is merely a display choice or an approved exclusion. A reviewer may hide a long description to make a sheet readable without intending to remove its stock quantity. Write the business meaning before selecting 9 or 109.
The referenced range. Even a suitable function number cannot include a detail row outside its references. Ask how appended stock records enter the range and review the actual delivered formula. A formula example in this article is not proof that an offered template expands safely when a new location or product is added.
The quantity unit. Cases, bottles and individual packs cannot be treated as interchangeable merely because their cells are numeric. Name the unit and any approved conversion outside the subtotal. Keep the row’s identifier and unit visible in the review evidence so the mathematical sum has a business interpretation.
Filter reproducibility. Preserve criteria such as location and status using the vocabulary actually present in the worksheet. “The current view” is fragile: another person can clear a filter or hide a different row. Record a review date and an evidence copy instead of asking future reviewers to reconstruct an undocumented screen state.
Vertical design. Microsoft describes SUBTOTAL as intended for columns of data. With SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. If locations run horizontally across columns, do not assume hiding a location column is equivalent to hiding a detail row in a vertical stock list.
Summary boundaries. Nested SUBTOTAL handling avoids one specific double-counting mechanism. It does not verify record uniqueness or reconcile stock transfers. Keep those checks separate; a plausible total can still contain duplicate detail or omit an entire period.
Best Smart Vending Machines
These three real WEIMI listings provide cabinet candidates for a procurement discussion. Product facts below come from public listings reviewed on 10 October 2026. Spreadsheet deliverables, report formats and calculation policies require separate confirmation in the quotation.
CANDIDATE 1 / Packaged-drink recognition workflow
Single-Door AI Vision Smart Fridge for Packaged Drinks
The listing describes camera-based recognition, five shelf levels with five baskets, and a top screen or lightbox arrangement. Treat it as a cabinet for compatible packaged products; it does not establish that drinks are prepared inside the machine. Confirm cooling and the precise ordered configuration.
For a worksheet proposal, ask whether quantities refer to recognised items, available stock or a manually reconciled count. Define the field’s meaning before adding it across visible rows. Neither recognition wording nor a named cloud system establishes an Excel SUBTOTAL implementation.
Review the public listing
CANDIDATE 2 / Mechanism-led snack and drink selection
WM22 Snacks and Drinks Vending Machine
The public page lists a 21.5-inch touchscreen, cooling and inventory management. It presents spiral, conveyor, direct-push and hanging choices as options. Confirm the actual dispensing mechanism for each quoted product mix; do not treat every option as installed in one cabinet.
A stock worksheet may require a mapping between product and dispensing position. Ask the reporting owner how that mapping survives review and what each quantity represents. A filtered subtotal can be correct while the position-to-product relationship still needs separate validation.
Review the public listing
CANDIDATE 3 / Expanded physical merchandising arrangement
Two Cabinets, More Choice: Snack & Drink Vending Station
The listing shows a main product-display cabinet with an additional visible spiral-stock area. This supports discussion of the physical arrangement. It does not prove combined software, independent cooling, a second screen or a specific capacity for the ordered system.
If the offer includes one worksheet covering both areas, require an explicit aggregation boundary. Ask whether rows describe separate physical stocks or one combined figure. Decide that question before choosing which filtered rows to sum; the cabinet photograph does not settle it.
Review the public listing
Feature Comparison
Keep physical-product evidence and reporting acceptance evidence in separate columns. An unverified worksheet capability should remain a question in the request for quotation, rather than becoming a positive score.
| Candidate |
Public listing basis |
Worksheet question |
Evidence limit |
| Single-Door AI Vision Smart Fridge for Packaged Drinks |
Camera recognition; shelf/basket arrangement |
What exactly is counted in each stock row? |
No verified Excel export or subtotal policy. |
| WM22 Snacks and Drinks Vending Machine |
Touchscreen; cooling; inventory management; mechanism options |
Which quoted position and product does a quantity describe? |
Options and reporting details need written confirmation. |
| Two Cabinets, More Choice: Snack & Drink Vending Station |
Main display plus visible additional spiral-stock area |
Does the report separate the two physical stock areas? |
No inferred combined platform or capacity. |
Cost & ROI Analysis
A subtotal dispute creates review work even when no cabinet component needs changing. Estimate that work separately from equipment cost. The following assumptions are invented planning values, not prices, customer results, savings forecasts or a business case for any of the three machines.
Assume two reviewers each spend 45 minutes agreeing the stock population and checking the four-row example, at an assumed labour rate of USD 32 per hour. The combined 90 minutes equals 1.5 hours, producing an illustrative review cost of USD 48. If a later disagreement requires one additional 30-minute review at the same rate, that adds USD 16.
A defined population may avoid some rework, but this article does not quantify an actual avoided order error. To estimate benefit responsibly, use your own count of disputes, time records and documented corrections. Do not multiply an illustrative quantity difference by an invented selling price and present it as recovered revenue.
Cabinet price, freight, installation, payment fees, maintenance and any reporting-service fees still need a quotation. A correct worksheet total is only one part of operational readiness. It cannot establish stock availability, demand, margin or payback by itself.
Best Choice by Scenario
Hidden rows remain in the review. If manual hiding serves only readability while the filtered population remains approved, consider SUM function number 9 for the vertical range. Clearly label that hidden included rows still contribute. Do not call the result “visible stock only.”
Only the currently nonhidden filter result counts. If reviewers explicitly agree that manually hidden rows leave this review population, consider 109. Preserve the reason for hiding each excluded record. An unrecorded display action should not become an unexplained reduction in an order.
Locations occupy columns. Redesign or evaluate the horizontal report according to its actual structure. Hiding a column does not create the same exclusion behaviour described for a vertical range with 109. Select equipment by the product mix and physical configuration while assigning workbook design to its actual owner.
Applications
A replenishment review can filter to a location and a defined stock status, then calculate quantities using the approved manual-hidden-row rule. Keep the filter label and unit beside the total. A different location review should be an explicitly different population, not merely a saved number with no scope.
A cabinet-expansion proposal can compare the stock allocated to existing and proposed areas. Before aggregation, establish whether those records represent separate physical inventories. A total across both areas is meaningful only after that boundary is agreed; a dual-cabinet listing supplies no report schema.
A handover worksheet can preserve a frozen evidence copy with the review criteria and formula. Later operational work may use a different filter, but it should not retroactively change what the handover total meant. This is a suggested documentation practice, not a feature verified in a WEIMI service.
FAQ
Does 9 include rows removed by a filter?
No. Microsoft states that SUBTOTAL ignores rows excluded by a filter regardless of function_num. The difference between 9 and 109 concerns manually hidden rows within the remaining vertical population.
When should I use 109?
Use it when the agreed review excludes manually hidden rows as well as filtered-out rows. First define that policy; 109 is not automatically the right choice for every stock approval.
Will hiding a column remove it from a horizontal subtotal?
Microsoft gives SUBTOTAL(109,B2:G2) as an example where hiding a column does not affect the subtotal. The function is designed for columns of data or vertical ranges.
Are all group totals ignored automatically?
The source specifically says nested SUBTOTAL calculations within the references are ignored. Do not assume manually entered totals or arbitrary other formulas receive that same treatment. Review the actual cells.
Does a correct subtotal prove accurate inventory?
No. It calculates the chosen population under the formula rules. Record freshness, product mapping, quantity units and duplicate detail remain separate review questions.
Do the three listed machines provide this workbook?
No such workbook capability has been verified here. The products are a public-listing shortlist. Ask the reporting provider to confirm any export format, template and calculation policy in the offer.
Final Recommendation
Approve the population before approving the total. For a vertical SUM subtotal, 9 includes manually hidden rows and 109 excludes them; both exclude filtered-out rows. Record the selected rule in words, then retain the formula, range and reproducible review state.
Use the shortlist to discuss the cabinet that fits your merchandising plan. Require a separate reporting commitment if an Excel stock worksheet is part of procurement. Public hardware descriptions and documented Microsoft formula semantics support different parts of the buying decision; neither establishes an unperformed integration test.
CTA
Send your packaged-product mix, preferred cabinet arrangement, quantity units and stock-review workflow. If you need a worksheet deliverable, state whether manually hidden records should remain in the approved total. Ask WEIMI to identify the equipment configuration and reporting items included in the quotation.
Get My Custom Quote
