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