How to SUMIF only Visible Cells In Excel

[@danrow-c7x]

Hello, do you have a COUNTIFS and a AVERAGEIF version of this formula ?

[@crispexcel]

For Countif
use below function
=COUNT(SUBTOTAL(103,OFFSET(C2,
ROW(INDIRECT("1:"&COUNTA(tblStaff[Gender]))),))
*(tblStaff[Gender]="Male"))
for averageif
Use below function
=AVERAGE(SUBTOTAL(103,OFFSET(C2,
ROW(INDIRECT("1:"&COUNTA(tblStaff[Gender]))),))
*(tblStaff[Gender]="Male")*tblStaff[Salary])

[@mayurrajude]

write the formula for the use

[@anshikasahu9193]

it's not working.

[@crispexcel]

Which part is failing

[@crispexcel]

Download worksheet here
www.crispexcel.com/how-to-sumif-data-ignoring-hidden-rows-in-excel/

[@anshikasahu9193]

@@crispexcel can you please check your email.

[@anshikasahu9193]

@@crispexcel After putting the formula, the result I'm getting is "6,127" and not "37,051" that is the total of visible male staff.

[@crispexcel]

@@anshikasahu9193 If you are not using office365 try Ctrl + Shift +Enter instead of just entering it