Countif on visible cells only as formula?

[royca]

I have a simple formula looking for "-1" in a column

=COUNTIF(G4:G60000,-1)

However, if I filter the column the count doesn't change.

Is there a way to have this formula count only the visible cells?

If not how will I accomplish this in VBA?

Thank you for any assistance.

 

[mvsub1]

=SUMPRODUCT(SUBTOTAL(3,OFFSET(G4:G60000,ROW(G4:G60000)-MIN(ROW(G4:G60000)),,1))*(G4:G60000=-1))

See here for the logic: http://www.mrexcel.com/forum/excel-questions/56984-countif-visible-cells-filter-mode.html