User Rating: 5 / 5

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.

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

"Open Image in New Tab" for a full and clear view.

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.

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.

Only One of the Multiple formulas is illustrated above. For full details and explanation refer to Ebook of Excel Formulas.