--- title: Budgeting Tip | Sum vs Subtotal and Cell Dependency --- Call us at [888.368.2463](tel:8883682463)    [CONTACT US](http://jmtconsulting.com/contact-us/) [![jmtconsulting](https://info.jmtconsulting.com/hubfs/JMTConsultingGroup%20-Aug2017/images/cropped-JMT-logo-RGB.png "jmtconsulting")](http://jmtconsulting.com/) - [Services](http://jmtconsulting.com/services/) - [Software Implementation](https://jmtconsulting.com/services/implementation/) - [Nonprofit Financial Reporting](http://jmtconsulting.com/services/nonprofit-financial-reporting/) - [Budget Management](http://jmtconsulting.com/services/budget-management/) - [Business Process & Workflow Improvement](http://jmtconsulting.com/services/business-process-workflow-improvement/) - [Systems Integration](http://jmtconsulting.com/services/systems-integration/) - [Business Intelligence & Management Reporting](http://jmtconsulting.com/services/business-intelligence-management-reporting/) - [Grant Management](http://jmtconsulting.com/services/grants-management/) - [Other Services](http://jmtconsulting.com/services/other-services/) - [Resources](http://jmtconsulting.com/resources/) - [Events](http://jmtconsulting.com/resources/calendar/) - [Upcoming Webinars](https://jmtconsulting.com/resources/calendar/?filter=webinars) - [All Events](https://jmtconsulting.com/resources/calendar/) - [Software Partners](http://jmtconsulting.com/software-partners/) - ERP - [Sage Intacct](http://jmtconsulting.com/software-partners/sage-intacct) - [MIP](http://jmtconsulting.com/software-partners/abila-mip/) - AP Automation - [AvidXchange](https://jmtconsulting.com/software-partners/avidxchange/) - [Bill.com](https://jmtconsulting.com/software-partners/bill-com/) - Budgeting & Planning - [Vena](https://jmtconsulting.com/software-partners/vena/) - [Sage Intacct Budgeting & Planning](https://jmtconsulting.com/software-partners/sage-intacct-budgeting-and-planning/) - Human Resources - [ADP Workforce Now](https://jmtconsulting.com/software-partners/adp/) - [Nexonia](http://jmtconsulting.com/software-partners/nexonia/) - [Workforce Go!](https://jmtconsulting.com/software-partners/workforcego/) - Close Management - [FloQast](https://jmtconsulting.com/software-partners/floqast/) - Cloud Application Hosting - [Ace Cloud Hosting](https://jmtconsulting.com/software-partners/ace-cloud-hosting/) - Cemetery Management - [Axiom Cemetery Management](https://jmtconsulting.com/software-partners/axiom/) - [Industry Partners](https://jmtconsulting.com/industry-partners/) - [About](http://jmtconsulting.com/about/) - [About JMT](http://jmtconsulting.com/about) - [Clients](http://jmtconsulting.com/about/clients/) - [Careers](http://jmtconsulting.com/about/career-opportunities/) - [Referral Program](https://jmtconsulting.com/about/referral/) - [Support](http://jmtconsulting.com/support/) ### BUDGETING TIP OF THE MONTH ## Sum vs Subtotal and Cell Dependency   Often times when working with large datasets for budgeting and forecasting, duplication of data occurs during consolidation of reports. This requires a thorough reconciliation of compiled data in order to maintain data integrity for analysis. The following will explain how to create a multi-function formula that can assist in reviewing datasets without manipulating them into a report.   Many Excel users have utilized the common **AutoSum** function, which simply totals selected cells in a worksheet. While this can work for most report calculations, another multi-use formula that gives user an additional layer of functionality is the **Subtotal** formula. This function not only adds values in a selected range, but also allows datasets to be refined using filters without redefining the formula parameters. In addition to adding values, the Subtotal function can also perform **Count**, **Average**, **Max**, **Min**, **Product**, **Standard Deviatio****n**, and **Variance** tasks. In the screen shot below, you can see both Sum and Subtotal formulas return the same value when applied to a range (formula reference in column I2,I3). ![sept-newsletter-budgeting-1](https://info.jmtconsulting.com/hs-fs/hubfs/sept-newsletter-budgeting-1.png?width=829&name=sept-newsletter-budgeting-1.png)   If a filter is applied to view 2019 data, you can see below the Sum formula remains the same, whereas the Subtotal returns the correct value based on the specified filter (formula reference in column I2,I3). ![sept-newsletter-budgeting-2](https://info.jmtconsulting.com/hs-fs/hubfs/sept-newsletter-budgeting-2.png?width=828&name=sept-newsletter-budgeting-2.png)   A helpful set of features in Excel to track cells impacting a formula are known as the **Trace Precedents** and **Trace Dependents**. In the screenshot below, the yellow highlight references the cell(s) that precede the formula, while the green highlight references dependent cell(s) for a formula. ![sept-newsletter-budgeting-3](https://info.jmtconsulting.com/hs-fs/hubfs/sept-newsletter-budgeting-3.png?width=1698&name=sept-newsletter-budgeting-3.png)   ### Have additional questions regarding budgeting and planning? Contact your CAM! © 2019 JMT Consulting Group, All Rights Reserved | 2200 Route 22, Patterson, NY 12563 | (888) 368-2463 [![linkedin-button](https://info.jmtconsulting.com/hs-fs/hubfs/linkedin-button.png?width=30&name=linkedin-button.png)](https://www.linkedin.com/company/jmt-consulting-group/)  [![twitter-logo](https://info.jmtconsulting.com/hs-fs/hubfs/twitter-logo.png?width=32&name=twitter-logo.png)](https://twitter.com/jmtconsulting)  [![facebook-logo-button](https://info.jmtconsulting.com/hs-fs/hubfs/facebook-logo-button.png?width=30&name=facebook-logo-button.png)](https://www.facebook.com/JMT-Consulting-Group-118847891526553/)