How to SUMIF only Visible Cells In Excel

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

[@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