The SUBTOTAL function in Excel allows users to create groups, and then perform various other Excel functions such as SUM, COUNT, AVERAGE, PRODUCT, MAX, etc. Thus, the SUBTOTAL function in Excel helps in analyzing the data provided. I see no difference in how the Subtotals feature [Data> Subtotals] operates in Excel 2011 compared to previous versions. To determine why it doesn't seem to be working for you, accurate details are needed.

Excel

Assuming the range you're passing to the function is correct, do you have any circular reference messages in the status bar, or calculation set to manual?No, there are no circular references. Calcs are set to automatic. In the section where the function does work I am just adding up values that come from a SUMIFS formula. In the section where the function is returning a 0 value I am using an if statement (if something equals something then the SUBTOTAL function, if not then a SUMIFS formula).

That seems to be the only difference, but I'm not sure if/why that would cause this problem. Perhaps it's not the subtotal that's the problem then. Maybe it's the 'If something equals something' part that is NOT true when you think it is (or vice versa).

Office 2016 mac use vlc for video. Powerpoint 2016 not able to open up media files I am using powerpoint 2016 with a MAC and not able to play any of the media slide videos. The message that is coming up is codec unavailable.

Use the formula evaluation tool to follow the steps of the formula. And it will probably help if you post your 'actual' formula.I'm pretty sure the formula is sound. Here is the formula I am using: =IF($D206='Subtotal',SUBTOTAL(109,F140:F205),SUMIFS(Sheet2!E:E,Sheet2!$A:$A,$B206,Sheet2!$C:$C,$D206)) My data looks like this A 1 A 2 A 3 A 4 A Subtotal B 1 B 2 B 3 B 4 B Subtotal.except there are a lot more rows than this. Many of the rows will have zero values, so I am filtering these out, thus the need of the subtotal. My formula is saying if the cell in the number column says 'Subtotal' then I subtotal up through all of the values with the same letter in the letter column. I know there are better ways to do this, but I am just trying to get a report done quickly and then I can refine it as I go.

Excel Tables have a Total Row that can be toggled on and off from the Ribbon. Several functions are available when the Total Row is turned on. In Excel 2010 and 2007 (Windows) select a cell inside the Table, then choose Table Tools and click the check box for Total Row. In Excel 2011 (Mac) select a cell in the Table and choose Tables, then select the check box for Total Row. A Total row appears below the data where each column has access to several automatic.

The default selection for the Total Row is none, meaning no function is selected when you first turn on the Total Row on your Table. Average, Count, Count Numbers, Max, Min, Sum, StdDev, and Var are options on the drop-down list for each cell in the Total Row. You can even choose More Functions, which we won’t go into here. When you choose a function from the list, what you really get is the SUBTOTAL function, where the first argument is the function, and the second argument is the Table column data. In the picture below, 101 represents the Average function, and [Net Product] is the data. Excel inserts this formula automatically when you select Average from the drop-down list.

Using SUBTOTAL in the Total Row means that when the Table is Filtered, the function will give the correct answer. If you toggle the Total Row off, Excel remembers the function and it will appear again when you toggle the Total Row back on. Total Row Reference in a Formula The Total Row can be used in a formula outside the Table. Notice the Avg Hours Per Setup formula below uses #Totals to refer to the Total Row in the Table. By using the the Setup Hours and Number Setups from the Total Row, you can Filter the Table and get the correct answer for Average Hours per Setup. The problem occurs when the Total Row is toggled off, which leaves this formula without a reference. If you recall from my on Tables, I used the following formula for Average Hours Per Setup =SUM(Table1[Setup Hours])/SUM(Table1[Number Setups]) which has a different flaw.

It does not work when the Table is Filtered. Create a Formula That Works We can replace SUM in this last formula with the SUBTOTAL function =SUBTOTAL(109,Table1[Setup Hours])/SUBTOTAL(109,Table1[Number Setups]). Now when we Filter the Table the calculation is correct and we don’t have to worry about having the Total Row toggled on for the formula to work. SUBTOTAL with Windows and Mac It’s much easier to use the SUBTOTAL Function with Windows versions of Excel (2007, 2010) because autocomplete gives you the different function numbers for the first argument. The Mac version (2011) doesn’t have autocomplete but you can access Help, which brings you to another window with some information that helps you decide which function number to use for SUBTOTAL.