I got the solution finally:

Pragmatic solution:
Use a helper column to extrat the number then use SUBTOTAL(); So say your helper column was column C you would use =SUBTOTAL(104,C3:C500) – by Scott Craner

why aggregate function doesn't ignore hidden cells:
Neither the aggregate or the subtotal works with the string parse, that needs to be done prior to the formula. – by Scott Craner