Subtotal with sumif(s)

[millawitch]

Hi all,

I have the following formula working but it looks a bit cumbersome - is there a way to streamline it?

=SUM(SUMPRODUCT(SUBTOTAL(9,OFFSET(Timekeeping[Units / Hours],ROW(Timekeeping[Units / Hours])-MIN(ROW(Timekeeping[Units / Hours])),0,1))*(Timekeeping[Date] > Billing!$A$2)),ROUND(SUMPRODUCT(SUBTOTAL(9,OFFSET(Timekeeping[Units / Hours],ROW(Timekeeping[Units / Hours])-MIN(ROW(Timekeeping[Units / Hours])),0,1))*(Timekeeping[Date] < =Billing!$A$2))/6,2))

Background: I need to have a total of hours recorded based on filtered columns, the formula very straight-forwardly used to be =SUBTOTAL(109,Timekeeping[Units / Hours]). After a certain date ("Billing!$A$2") timekeeping has been recorded in hours, whereas before it was recorded in units (1 unit = 10 min). To avoid having to convert numbers from one or the other every time I add new entries to the "Timekeeping" table, I came up with this formula.

Thanks!

 

[oldbrewer]

make up 10 rows of pretend data unfiltered and tell us what you want - please