Formula for Extraciting the Year from the Date Column in a Table

  • Hi Experts


    I have the following formula:

    Code
    =SUMPRODUCT(SUMIFS(INDIRECT(INDEX(INDIRECT("All"),0)&"[Total]"),INDIRECT(INDEX(INDIRECT("All"),0)&"[Centre]"),"="&$D$1,INDIRECT(INDEX(INDIRECT("All"),0)&"[Qtr]"),"="&$F$2,INDIRECT(INDEX(INDIRECT("All"),0)&"[Category]"),C6))


    However I need to incorporate the Year of the Date if the Year is equal to H2. I have tried:

    Code
    =SUMPRODUCT(SUMIFS(INDIRECT(INDEX(INDIRECT("All"),0)&"[Total]"),YEAR(INDIRECT(INDEX(INDIRECT("All"),0)&"[Date]")),"="&H2,INDIRECT(INDEX(INDIRECT("All"),0)&"[Centre]"),"="&$D$1,INDIRECT(INDEX(INDIRECT("All"),0)&"[Qtr]"),"="&$F$2,INDIRECT(INDEX(INDIRECT("All"),0)&"[Category]"),C54))


    but this produces the error. "Your formula contains an error"


    I need to extrapolate the Year from

    Code
    INDIRECT(INDEX(INDIRECT("All"),0)&"[Date]"


    How do I do this please?



    Thanks

  • Re: Formula for Extraciting the Year from the Date Column in a Table


    Attaching a sample workbook would help. :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: Formula for Extraciting the Year from the Date Column in a Table


    Thanks AliGW,


    The sheet is quite complex with VB etc.


    I have thought of the obvious workround, which is to add a column "Year" to the table, and therefore the rest should be easy, just add an extra sumif Table[Year] = H2.


    However I have attached the file anyway, so then hopefully I can learn.


    There are hidden sheets too.


    Thanks

Participate now!

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