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"


    The formula in O8 should be in :


    Sorry tor the confusing