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

    I think your copy down formula in O8 will give wrong result,


    your O25 formula result show in 261, but it should be in 142.


    So,


    Your O8 formula could be shortly revised in :


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


    Regards

    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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login