If you have a list of invoices in Excel, and you want that list to show a total for each month and for the year, most people would use the SUM function to total each month. But if you try to do that for the year you will end up totaling both the invoices and the monthly totals, unless you move the monthly totals to a separate column.\u00a0 Another common approach is to write a formula that points to each monthly total and adds them up.\u00a0 The beauty of the SUBTOTAL function is that you can add up the whole column and it will ignore the other SUBTOTALS that finds.<\/p>\n
Here is an example of how it works:<\/p>\n
Let’s say that I want a subtotal in cell C4 that adds up the 3 cells above it.\u00a0 I would enter the formula =SUBTOTAL(9,C1:C3) in cell C4.\u00a0 As you would expect, the “C1:C3” designates the range of cells from C1 through C3. I’ll explain the 9 later.<\/p>\n
Next I want another subtotal in C9 that adds up the 3 cells above it.\u00a0 I would enter the formula =SUBTOTAL(9,C6:C8) in cell C9.<\/p>\n
Now, to put a grand total on line 11, I can use the formula =SUBTOTAL(9,C1:C10).\u00a0 Notice that the range doesn’t exclude cells C4 and C9 where the other subtotals are.\u00a0 The subtotal function automatically excludes those amounts.<\/p>\n
In a small example like this, it may not seem worth the trouble of trying to remember how to enter the subtotal function.\u00a0 But in a large spreadsheet with hundreds or even thousands of lines, you can save a lot of time and effort by not having to track down the individual ranges that would be needed to use the more familiar SUM function.\u00a0 Next month I will show you how, in many cases, you can have Excel insert the subtotals and grand total automatically, so you don’t have to remember how to enter the subtotal function yourself.<\/p>\n
Now, back to the mysterious “9” that I said I would explain.\u00a0 The subtotal function has 11 different options that can be chosen.\u00a0 Among other things, it can add, multiply, count, or average, the entries in a given range of cells.\u00a0 The 9 simply tells Excel to add or Sum the cells in the range.\u00a0 For a complete list of options, search for SUBTOTAL FUNCTION in Excel’s help.<\/p>\n","protected":false},"excerpt":{"rendered":"
You often need to have subtotals in a spreadsheet, but if you are not careful, you can end up accidentally including your subtotals in the grand total. The Subtotal function in Excel, can make it faster and easier to avoid that problem.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[5],"tags":[],"_links":{"self":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/110"}],"collection":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/comments?post=110"}],"version-history":[{"count":2,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/110\/revisions"}],"predecessor-version":[{"id":563,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/110\/revisions\/563"}],"wp:attachment":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/media?parent=110"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/categories?post=110"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/tags?post=110"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}