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

Download Worksheet

Print Friendly, PDF & Email

Do you want to excel in Excel?

Or

Seeking Microsoft Certification?

Every Week Receive Resources To Get you Closer to Your Goal!

No Huff! No Fluff! No Spam!

You are on your way to Excelling in Excel and Becoming Microsoft Certified!

Share This

Spread the Good News!

Do me favour, please? Share this with your friends!

Thanks For Commenting!

Awesome for you to comment! Please share the post with your friends.