Print

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

SUMIF using SUBTOTAL function to Exclude Cells Hidden by a Filter 

 


 

Sumif / Countif using SUBTOTAL function to Exclude / Include Cells Hidden by a Filter.

  

Cell D6 formula: SUMIF to EXCLUDE cells hidden by using Filter. 

    

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

 

SUMIF Exclude Hidden Filtered Rows  

 

Cell D6 formula SUMIF to EXCLUDE cells / rows hidden by Filter.

 

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

 

The SUBTOTAL function ALWAYS ignores / excludes any rows hidden by a filter, irrespective of its function number (argument), so that function_num argument of 109 or 9 will act similarly and so will function_num argument of 103 or 3.

 

Rows 5 & 11 are hidden with AutoFilter:   A5 =81, A11 =81;   B5 = "C", B11 = "A". 

 

Cell D6 formula: 

  

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

  

Explanation - Cell D6 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 by using Filter - {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 by using Filter (zero is returned for these hidden cells) - {79;80; 79;0; 80;79; 80;82; 83;0; 0;0;0; 0;0;0; 0;0;0}. Using this with (B2:B20="A") will return values only for those cells where column B = "A" - {79;0; 79;0; 80;0; 80;0; 83;0; 0;0;0; 0;0;0; 0;0;0} - 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 by using Filter. Count Unique Values (using SUBTOTAL) to EXCLUDE cells / rows hidden by using Filter.

 

 

Cell D3 formula: Use SUBTOTAL to Sum & EXCLUDE cells hidden with Autofilter.

 

Cell D4 formula: Use SUM will Total all cells - INCLUDES hidden cells / rows.

 

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

 

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

 

Cell D10 formula: COUNTIF to EXCLUDE cells / rows hidden with Autofilter: use SUBTOTAL with SUMPRODUCT to COUNT column A values where column B = "A". 

 

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

 

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

 

Cell D15 formula: Count Unique Values (using SUBTOTAL) in range A2:A20, EXCLUDE cells / rows hidden with Autofilter - Array Formula (Ctrl+ Shift+ Enter).

 

Cell D16 formula: Count Unique Values (using SUBTOTAL) in range A2:A20, EXCLUDE cells / rows hidden with Autofilter - Array Formula (Ctrl+ Shift+ Enter).

 

Cell D17 formula: Count Unique Values (using SUBTOTAL) in range A2:A20, EXCLUDE cells / rows hidden with Autofilter - Non-Array Formula.

 

Cell D18 formula: Count Unique Values (using SUBTOTAL) in range A2:A20 if B2:B20 = "A", EXCLUDE cells / rows hidden with Autofilter - Array Formula (Ctrl+Shift+Enter).

 

Cell D19 formula: Count Unique Values (using SUBTOTAL) in range A2:A20 if B2:B20 = "A", EXCLUDE cells / rows hidden with Autofilter - Array Formula (Ctrl+ Shift+ Enter). 

 

Cell D20 formula: Count Unique Values in range A2:A20, INCLUDE hidden cells / rows - Array Formula (Ctrl+Shift+Enter).

 

Cell D21 formula: Count Unique Values in range A2:A20, INCLUDE hidden cells / rows - Non-Array Formula.