Week number formula covering more than one year

[TomParkD]

I have an array formula that collates costs for eg travel expenses against a week number eg 28/8/14= week 25. Everything works fine until I have the same week number in the following year. The array now adds everything for week 25 for 2014 and 2015 and puts the grand total into week 25 in both 2014 and 2015. How do I get the separate totals for week 25 for 2014 and 2015?

 

[Yarek]

Hi,
Have you tried adding IF(year=2014) somewhere in your formula?

Having full formula would help as well

Regards

 

[TomParkD]

Hi,
Have you tried adding IF(year=2014) somewhere in your formula?

Having full formula would help as well

Regards

The formula is

sum(((NominalCode=$c100)+(week number=f$98))=2)*NetAmount)

i will try what you suggest

Thanks!

 

[Yarek]

Legend:
formulas
values


I believe this should be ok:

sum(((NominalCode=$c100)+(if(year_number=''2014'';week_number=f$98)))=2)*NetAmount)

or having ''2014'' in some different cell so it can be changed quickly:

=sum(((NominalCode=$c100)+(if(year_number=required_year;week_number=f$98)))=2)*NetAmount)

where required_year is located in a cell like A1.


or for current year

=sum(((NominalCode=$c100)+(if(year_number=year(today());week_number=f$98)))=2)*NetAmount)


Hope this helps