Good afternoon all,
I am building a customer template to import system generated reports. I am slowly getting there but I have hit an annoying obstacle that I am unsure how to get around.
I have attached the a sample file.
And I know having subtotals in the middle of the table is an awful practice but there is no way around it as it is auto generate report.
Basically, I am using an IF statement (O column) to tackle this and to basically sum totals for hours (will use it for total cost too), if the C column has "Total".
The current formula is
=IF(C12="Total",SUM(OFFSET(IF([@[Employee Name]]="Total",[@[Total Hours]],""),-1,0,-COUNTA(B8:$C12))),G12+N12)
with B8 part being the one I can't figure out.
P.s I am not very experienced with formulating my formulas so I completely get that it might be ugly
I think in its essence it works, however what I can't figure out is the COUNTA part of the formula as I need the formula to automatically get to first cell is column B that is not blank. In the uploaded file I have just free-typed "B8" because that is the part I can't figure out.
Because I'd like the formula to find/use the range between every "Total" in column C and the first cell in column B above it, that isn't empty.
Hopefully this makes sense from the upload file. (I have dragged the formula down to every other cell in the O column, but you can see how my "free-typing" makes it from for other totals.
Also, I want the formula to be able to handle the fact that I have a macro that inserts an empty table row above "Total" if I double click it, so I'd want the formula to be able to count for it too.
I hope this makes sense but do let me know if it doesn't and I will try to explain. Also a lot of thanks in advance to anyone who spends their time and tries to help me out; greatly appreciate it