staff timesheet

[estelle]

Hi. I am designing a new staff rota and need to calculate hours and minutes from a selection of dates for the whole year. When I use the formula =SUMIF(F3:F563,">0")/60 this does exactly what want for the whole but I cannot work out how to show a subtotal when I filter the list to show a range of dates from the full list still shows the total hours and parts thereof.

 

[AlanY]

you can try the subtotal() with function 109, sum to ignore hidden cells, i.e.

=subtotal(109,F3:F563)/60

 

[estelle]

Can't believe I didn't try this . Thank you so much, works perfectly

 

[AlanY]

you're welcome

 

[estelle]

Tried this again and although it works beautifully is there any way to get it to ignore any cells which include text as well as blanks. There is a dropdown in each cell where you can either pull off times or text items so I need it to not try to add the text. What I am getting is #VALUE !

 

[AlanY]

try this instead

=AGGREGATE(9,3,F3:F563)/60

 

[estelle]

Works beautifully. Many, many thanks

 

[AlanY]

you're welcome