OFFSET combined with COUNTA

  • 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 :D


    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 :)


    Demo file for Macro help.xlsx

  • Hi Bosco_yip,


    Thank you for your reply - I have tried to use the formula you provided but the "Total" rows come up with no value, any chance you.

    Those references to column T slightly confuse me, any chance you could elaborate?


    Thanks in advance

  • I tested your data in "Column T ", and forget bring it back to "Column O"


    Therefore,

    The formula in O8 should be in :


    =IF(C8<>"Total",G8+N8,SUM(INDEX(O$7:O7,MATCH("zz",B$7:B7)):O7))


    Sorry tor the confusing


    Regards