Using SUBTOTAL formula within an IF formula

  • I have a table of data that I summarize by date (Column B) and by "Month Ending" label (Column A) in columns to the right of the data. The dates in the table are dynamic, so the "Month Ending" label will change positions depending on another variable, so I can't use the Subtotal function; the subtotal row will be in different locations. I'm using IF formulas to check for the "Month Ending" label related to the row the IF formula is in. Example formula in Cell K24:

    =IF(A24="Month Ending",I24,

    [if the current row 24 is the Month Ending row, then the value in Cell K24 is equal to the value in Cell I24]

    IF(A23="Month Ending",SUBTOTAL(9,K$2:K23),

    [if the previous row 23 is the Month Ending row, then the value in Cell K24 is the SUBTOTAL of all rows above Row 24]



    I'm doing this in four columns (K thru N) with with the first IF formula in each column a different formula summarizing different values. This formula works fine until I copy it into the other cells in the column; then the subtotals are all 0.

    Any ideas?

    Edited once, last by davec0864: format was wrong ().

  • If you are copying to other columns, then your reference to column A cells should be preceded with a $, like:
    =IF($A23="Month Ending",SUBTOTAL(9,K$2:K23)

    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

Participate now!

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