 # 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. • 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!