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.
———————————————————————————————–
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.