YTD formulas

[Tom22]

Hi


I am looking for a formula which gives me YEAR till date sum of numbers according to the given cretaria like in attached file I want YTD sum for Austria and for dept Fin

My YTD number should also change as when I change month in cell A2.


File attached for reference.


Many thanks In advance

 

[Peter Bartholomew]

The first solution should work now. The second relies on a new Office 365 feature and should start to work some time over the next few months.
Both solutions rely upon selecting the data that corresponds to the year to date
= INDEX(Data, ,1) : INDEX(Data, , MATCH(Month,Data[#Headers],0))
using the range composition operator ':'

The manner in which the data from the wrong country or department is filtered out is different. The first simply multiplies unwanted values by zero
= YearToDate*(Attribute[Country]=Country)*(Attribute[Dept]=Dept)
whereas the second filters the rows using these criteria prior to summation
= SUM(filteredData)

 

[bosco_yip]

Or,

In E7, enter formula :

=SUMPRODUCT((COLUMN(A$1:L$1)<=MONTH(1&A$2))*(Data!E$4:E$23=C7)*(Data!F$4:F$23=D7),Data!G$4:R$23)

Regards
Bosco

 

[Tom22]

Thanks Peter and bosco....it is working