Dynamic sum
 

I have a list of data that I download every week. Product code in col A,
product starting date in col B (this is not a calendar date, it's a week
number: term1 week 1, t1w2, etc) So col B actually will have the terms from 1
to 13. Col C will have the weeks (1-4). Col D will have the sales values. I
need to sum the sales for 13 terms or 52 weeks. Each product has a different
starting date. Once the sales for the 52 weeks get summed, start summing the
next 52 weeks/13 terms.
How can I do that dynamically?

Thanks.
--
when u change the way u look @ things, the things u look at change.

Dynamic sum
 

Make use of column E in your data sheet by using this formula in E2:

="t"&B2&"w"&C2

and copy this down to cover your data. Then in another sheet you could
have a table made up like this:

t1w1
t1w2
t1w3
t1w4
t2w1

and so on. Assume this is in column A, then in B1 of this sheet you
can have a formula like:

=SUMIF(Sheet1!E:E,A1,Sheet1!D:D)

and copy this down the 52 rows.

Hope this helps.

Pete

On Dec 6, 11:26 pm, sahafi wrote:
I have a list of data that I download every week. Product code in col A,
product starting date in col B (this is not a calendar date, it's a week
number: term1 week 1, t1w2, etc) So col B actually will have the terms from 1
to 13. Col C will have the weeks (1-4). Col D will have the sales values. I
need to sum the sales for 13 terms or 52 weeks. Each product has a different
starting date. Once the sales for the 52 weeks get summed, start summing the
next 52 weeks/13 terms.
How can I do that dynamically?

Thanks.
--
when u change the way u look @ things, the things u look at change.

Dynamic sum
 

Thanks, But how would it know where to assign each value for each product
code? The formula as is will return zero.

Thanks.
--
when u change the way u look @ things, the things u look at change.


"Pete_UK" wrote:

Make use of column E in your data sheet by using this formula in E2:

="t"&B2&"w"&C2

and copy this down to cover your data. Then in another sheet you could
have a table made up like this:

t1w1
t1w2
t1w3
t1w4
t2w1

and so on. Assume this is in column A, then in B1 of this sheet you
can have a formula like:

=SUMIF(Sheet1!E:E,A1,Sheet1!D:D)

and copy this down the 52 rows.

Hope this helps.

Pete

On Dec 6, 11:26 pm, sahafi wrote:
I have a list of data that I download every week. Product code in col A,
product starting date in col B (this is not a calendar date, it's a week
number: term1 week 1, t1w2, etc) So col B actually will have the terms from 1
to 13. Col C will have the weeks (1-4). Col D will have the sales values. I
need to sum the sales for 13 terms or 52 weeks. Each product has a different
starting date. Once the sales for the 52 weeks get summed, start summing the
next 52 weeks/13 terms.
How can I do that dynamically?

Thanks.
--
when u change the way u look @ things, the things u look at change.

Dynamic sum
 

You said in your first post that you needed to sum the sales for 52
weeks, but now you seem to want a breakdown by product code. Can you
give an example to show what you want to end up with?

Pete

On Dec 7, 5:05 pm, sahafi wrote:
Thanks, But how would it know where to assign each value for each product
code? The formula as is will return zero.

Thanks.
--
when u change the way u look @ things, the things u look at change.



"Pete_UK" wrote:
Make use of column E in your data sheet by using this formula in E2:

="t"&B2&"w"&C2

and copy this down to cover your data. Then in another sheet you could
have a table made up like this:

t1w1
t1w2
t1w3
t1w4
t2w1

and so on. Assume this is in column A, then in B1 of this sheet you
can have a formula like:

=SUMIF(Sheet1!E:E,A1,Sheet1!D:D)

and copy this down the 52 rows.

Hope this helps.

Pete

On Dec 6, 11:26 pm, sahafi wrote:
I have a list of data that I download every week. Product code in col A,
product starting date in col B (this is not a calendar date, it's a week
number: term1 week 1, t1w2, etc) So col B actually will have the terms from 1
to 13. Col C will have the weeks (1-4). Col D will have the sales values. I
need to sum the sales for 13 terms or 52 weeks. Each product has a different
starting date. Once the sales for the 52 weeks get summed, start summing the
next 52 weeks/13 terms.
How can I do that dynamically?

Thanks.
--
when u change the way u look @ things, the things u look at change.- Hide quoted text -

- Show quoted text -