User Rating: 3 / 5

Star ActiveStar ActiveStar ActiveStar InactiveStar Inactive
 

 

SUMIF using SUBTOTAL function to Exclude Cells Hidden with the Hide Rows command.

 


 

Sumif / Countif using SUBTOTAL function to Exclude / Include Cells Hidden with the Hide Rows command. 

 

Cell D8 formula: SUMIF to EXCLUDE cells hidden with the Hide Rows command. 

    

-----------------------------------------------------------------------------------------------

 

SUMIF Exclude Hidden Rows 

  

Cell D8 formula SUMIF to EXCLUDE cells / rows hidden with the Hide Rows command.

 

Uses SUBTOTAL with OFFSET and SUMPRODUCT to SUM column A where column B = "A".

 

The SUBTOTAL function INCLUDES any rows which may be hidden using the Hide Rows command, using its function number (function_num argument) constants 1 to 11 (use constants 101 to 111 to ignore / exclude hidden values).

 

The SUBTOTAL function ALWAYS ignores / excludes any rows hidden by a "filter", irrespective of its function number (argument).

 

Rows 5 & 11 are hidden with the Hide Rows command:   A5 =81, A11 =81;   B5 = "C", B11 = "A" 

 

Cell D8 formula: 

  

=SUMPRODUCT(( B2:B20="A")* SUBTOTAL(109, OFFSET(A2, ROW(A2:A20)- ROW(A2),)))  

  

Explanation - Cell D8 formula (Non-Array Formula): 

 

1) OFFSET(A2,ROW(A2:A20)-ROW(A2),): returns all values in column A (A2:A20) starting from cell A2, including the cells hidden with the Hide Rows command - {79;80; 79; 81;80; 79; 80;82; 83; 81;0; 0;0; 0;0; 0;0; 0;0}.

 

2) SUBTOTAL(109, OFFSET(A2, ROW(A2:A20)- ROW(A2),)): returns all values in column A (A2:A20) starting from cell A2, excluding the cells hidden with the Hide Rows command (zero is returned for these hidden cells). Using this with (B2:B20="A") will return values only for those cells where column B = "A", and using SUMPRODUCT returns the SUM of these cells. The function_num argument of 109 represents the SUM function and ignores hidden values.

  

 

-----------------------------------------------------------------------------

 

Related Formulas - illustrated in the Ebook of Excel Formulas.

 

 

Other formula(s): Formulas for Sum, SumIf, CountIf, Count Uniques: Using SUBTOTAL function to Exclude / Include Cells Hidden with the Hide Rows command. Count Unique Values (using SUBTOTAL) to EXCLUDE cells / rows hidden with the Hide Rows command.

 

 

Cell D3 formula: Use SUBTOTAL to Sum & EXCLUDE cells hidden with the Hide Rows command.

 

Cell D4 formula: Use SUBTOTAL to Sum & INCLUDE cells hidden with the Hide Rows command.

 

Cell D6 formula: Uses SUM will Total all cells - INCLUDES hidden cells / rows.

 

Cell D15 formula: SUMIF to INCLUDE cells / rows hidden with the Hide Rows command: use SUBTOTAL with SUMPRODUCT to SUM column A where column B = "A".

 

Cell D20 formula: SUMIF to INCLUDE hidden cells / rows: use SUMIF to SUM column A where column B = "A". 

 

Cell D21 formula: SUMIF to INCLUDE hidden cells / rows: use SUMPRODUCT to SUM column A where column B = "A". 

 

Cell D23 formula: COUNTIF to EXCLUDE cells / rows hidden with the Hide Rows command: use SUBTOTAL with SUMPRODUCT to COUNT column A values where column B = "A".

 

Cell D28 formula: COUNTIF to INCLUDE cells / rows hidden with the Hide Rows command: use SUBTOTAL with SUMPRODUCT to COUNT column A values where column B = "A".

 

Cell D32 formula: COUNTIF to INCLUDE hidden cells / rows: use COUNTIFS to COUNT column A values where column B = "A".

 

Cell D33 formula: COUNTIF to INCLUDE hidden cells / rows: use SUMPRODUCT to COUNT column A values where column B = "A".

 

Cell D35 formula: Count Unique Values (using SUBTOTAL) in range A2:A100, EXCLUDE cells / rows hidden with the Hide Rows command - Array Formula (Ctrl+ Shift+ Enter).

 

Cell D57 formula: Count Unique Values (using SUBTOTAL) in column A if column B = "A", EXCLUDE cells / rows hidden with the Hide Rows command - Array Formula (Ctrl+ Shift+ Enter). 

 

Cell D59 formula: Count Unique Values (using SUBTOTAL) in column A, INCLUDE cells / rows hidden with the Hide Rows command - Array Formula (Ctrl+ Shift+ Enter).

 

Cell D62 formula: Count Unique Values in column A, INCLUDE hidden cells / rows - Array Formula (Ctrl+Shift+Enter).