Are you looking for an alternative to the Sum function that can also ignore hidden values? Then look no further than the Subtotal function. The Subtotal is a versatile function that:
- Can be used for a wide range of numerical functions
- Ignores other Subtotals within the specified data range
The SUBTOTAL function syntax has the following arguments:
Function_num Required. The number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list.
Ref1 Required. The first named range or reference for which you want the subtotal
Ref2, Optional. Named ranges or references 2 to 254 for which you want the subtotal
Applies To: MS Excel 2003, 2007 and 2010:
- This tip will be based on the screen shot below where rows 7-9 are hidden
2. By using the Sum function the total product sales value will include hidden values
- Select cell G20 and enter the following formula =SUM(G4:G19)
- The answer will be $14,106.79 –this value includes hidden values
3. To calculate the total product sales value that ignores hidden values
- Select cell G21 and enter the following formula =SUBTOTAL(109,G4:G19)
- The answer will be $13,867.93- this value ignores hidden values
- The table below provides more details on the function numbers and the respective functions
If you have any suggestions for an Excel Tips & Tricks topic, or you would like help with a particular function, please email enablement@alchemex.com and yours could be the next Tip of the Week.
Alchemex provides examples of MS Excel procedures for illustration only, without warranty expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The MS Excel procedures on this web site are provided “as is” cannot be guaranteed that they can be used in all situations
