Add up certain cells in a row depending on 2 parameters

[Tradesman]

Hi, I received help putting together some sheets.

Ive figured a bit out but I am stuck.

heres my question

I have 3 rows of data for the same product.

I have 2011,2012 and 2013 ( 2013 all zeros so far!)

I want to be able to total a number of weeks up based on
a) the starting week no. ( a variable I wnat to add)
b) for a variable no. of weeks.

I would also be able for this to wrap around from week 52 to week 1 - for example

starting week week 45
no. of weeks 10

should start on week 45 and add up to week 2

Im Stuck!.xlsx

( I have unlinked some fo the data files )

In a perect world these chosen weeks would also format grey and underlined font!

[Jakobshavn]

Attached is a partial solution only. (can't handle wrapping)

[Tradesman]

Nicely done- could the wrapping involve some function that goes
IF week varaible+10>52 Then week variable -52 becomes the following weeks end week- if false then zero

eg week 44
+10=54
54-52=2
then do the same formual from 1 to 2??

[Jakobshavn]

The problem lies in the organization of the data.

It is perfect for the human reader, not so good for Excel.

I will update this post later with an alternative!

[Jakobshavn]

Here is the alternative. The data is re-formatted to pure columns. The "Marker" column contains the logic to determine which rows are to be summed.

[Tradesman]

Im stuck V3 2011-2012.xlsx

Hi Jakobshavn,

I can see what and how you have overcome the problem of wrapping- however the data really needs to be visualised as laid out so moving to continuos colomn doesnt help as I want to compare previous years to this one?

on a week to week basis for over 200 products!

maybe I could lay the data out like you ahve suggested then produce a visulaied version-Ive added my current version without wrapping for you

[Tradesman]

Hers a thought- if we went to the single colomn view for the data-

can I still represnt it using the seprate year - part no. and then week numbers along the colomns??

product abc2011
2012
2013

but entering data into a continous colomn?

[Jakobshavn]

YES!

A great idea.

Check back later for an example.

[Tradesman]

I could pick out the year from the date using right9year,2) into a colomn and then use it a sa vaiable in sumif??? -

can you use 2 varibales in sumif?

[Tradesman]

Im stuck unit sales anlysis 2011-2013 version 4.xlsx

Ok so Ive done some work and its getting there-

The only thing I cant get my head around is how I add up weeks that go across one year to another eg week 40 2011 to week 5 2012-then add up to compare week 40 2012 to week 5 2013?

your thoughts are greatly received!

( great idea for the single set of data !!! makes it easy to update as well)

[Jakobshavn]

Let me review your latest post.

[Tradesman]

Ok so the 52 wrap around is probaly a bit tuff-it is for me to figure out anyway!