Error when adding amounts in week 1 that spans 2 years

[TomParkD]

I have an array formula below that adds transactions for each week. This works OK until I come to a week1 which starts on 30 December 2013 and ends 5 January 2014. My work sheets spans more than 1 year, 2013-2015. The answer that I get takes only the transactions in 2014 ie week 1, 1-5 January 2014 and week 1 which starts 29 December 2014 and ends 31 December 2014. How can get the formula to take week 1 being the last part of December 2013 and the first part of January 2014?

=[TABLE="class: cms_table, width: 86"]
<tbody>[TR]
[TD] SUM(((NominalCodes=$D129)+(WeekNumberTB=(J$11))=2)*NetAmount*(CalendarYear=YEAR(J$12)))[/TD]
[/TR]
</tbody>[/TABLE]


j12= 05/01/14

Thanks for your help!

 

[Joyner]

Hello, I would have to see a sample of your data, but I suspect you could use SUMIFS instead of an array formula.

Can you use a helper column in your data, that would probably be the quickest and easiest solution to use a formula to determine the year. If not, you may be able to use something like CalendarYear=MIN(YEAR(J$12),YEAR(beginning date cell reference (where 30 December 2013 is stored)), but I am really just guessing not knowing your data and layout.

 

[TomParkD]

Hello, I would have to see a sample of your data, but I suspect you could use SUMIFS instead of an array formula.

Can you use a helper column in your data, that would probably be the quickest and easiest solution to use a formula to determine the year. If not, you may be able to use something like CalendarYear=MIN(YEAR(J$12),YEAR(beginning date cell reference (where 30 December 2013 is stored)), but I am really just guessing not knowing your data and layout.

The formula - same for every week- looks for WeekNumber - and the calendar year Week 51 is OK (22-Dec-13) as with the year formula it can only be in the year 2013. In the year 2014 week 1 ending 5-Jan-14 has transactions in 2014 but week 1 ending 4-Jan-15 also has transactions in 2014. My formula adds all 2014 transactions

[TABLE="width: 238"]
<tbody>[TR]
[TD="align: right"]51[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]22-Dec-13[/TD]
[TD="align: right"]29-Dec-13[/TD]
[TD="align: right"]05-Jan-14[/TD]
[/TR]
[TR]
[TD] (2,069.01)[/TD]
[TD] 1,343.49 [/TD]
[TD] (2,069.01)[/TD]
[/TR]
[TR]
[TD] (1,720.88)[/TD]
[TD] 8,288.01 [/TD]
[TD] (10,593.48)[/TD]
[/TR]
</tbody><colgroup><col span="2"><col></colgroup>[/TABLE]


[TABLE="width: 234"]
<tbody>[TR]
[TD="align: right"]51[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]21-Dec-14[/TD]
[TD="align: right"]28-Dec-14[/TD]
[TD="align: right"]04-Jan-15[/TD]
[/TR]
[TR]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD] (28,355.78)[/TD]
[TD] 107,098.00 [/TD]
[TD] (42,063.81)[/TD]
[/TR]
</tbody><colgroup><col><col span="2"></colgroup>[/TABLE]











[TABLE="width: 238"]
<tbody>[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]


[/TD]
[/TR]
</tbody><colgroup><col span="2"><col></colgroup>[/TABLE]

 

[Joyner]

From what you posted and explained above, I'm still not sure what you need and if one of the posted examples is okay and one is not.

In both examples, since the beginning date is from the prior year (starting dates are 30 dec 13 and 29 dec 14 respectively, do those amounts belong to year 2013 and year 2014 respectively?

Where it is week 1, are you saying those are actually week 52 for years 2013 and 2014?

To get the Calendar Year in the formula, can you use something like:

CalendarYear=MIN(YEAR(J$12),YEAR(J$12-6))

 

[PichaiTC]

Just remove CalendarYear part, is it work?

=SUM(((NominalCodes=$D129)+(WeekNumberTB=(J$11))=2)*NetAmount)