SUMIF Data Ignoring Hidden Rows in Excel is not as simple as other types of summation using criteria.
This is because the SUMIF function does not ignore hidden rows.
The solution comes from using a combination of SUBTOTAL & OFFSET functions to return only visible rows
Using the example below, sum the total salaries for only the visible male staff.
Note: rows between 4 & 8 , 12 & 17 are hidden.
Step 1: Use the OFFSET function to get an array of references for all rows.
OFFSET(C2,
ROW(INDIRECT("1:"&COUNTA(tblStaff[Gender]))),)
Step 2: Use the SUBTOTAL function constants between 101 to 111 as these ignore hidden rows. This returns an array of 1 & 0 where 1 represents the visible row’s reference.
SUBTOTAL(103,OFFSET(C2, ROW(INDIRECT("1:"&COUNTA(tblStaff[Gender]))),))
Step 3: Check if the gender meets your criteria. This returns an array of TRUE/FALSE
(tblStaff[Gender]="Male")
Step 4: Multiply these arrays with the salary data and sum them up
=SUM(SUBTOTAL(103,OFFSET(C2, ROW(INDIRECT("1:"&COUNTA(tblStaff[Gender]))),)) *(tblStaff[Gender]="Male")*tblStaff[Salary])
See step by step video below
Recent Comments