COUNTIFS to ignore hidden rows excel 2007

Question

would appreciate help with this one. Made more tricky because it concerns Time. I currently have

=COUNTIFS(Data!A:A,">="&TIME(0,0,0),Data!A:A,"<"&TIME(1,0,0))

but it doesn't work when I hide rows.

I've managed to work out a similar issue for other data which now uses

=SUMPRODUCT(SUBTOTAL(102,OFFSET(Data!$H$1,ROW(1:10000)-1,))*(Data!$H1:$H10000<35))

but I can't work out the syntax for my Time calculation.

Answer 1

You can just add the time criteria in to that second formula, e.g.

=SUMPRODUCT(SUBTOTAL(102,OFFSET(Data!$H$1,ROW(1:10000)-1,))*(Data!$A1:$A10000>=TIME(0,0,0))*(Data!$A1:$A10000<TIME(1,0,0)))

...but if you are using hour long "buckets" it might be easier to use HOUR function, but you can't apply HOUR to text values ,so if row 1 contains headers start at row 2

=SUMPRODUCT(SUBTOTAL(102,OFFSET(Data!$H$2,ROW(1:9999)-1,))*(HOUR(Data!$A2:$A10000)=0))