Every week I have to calculate year-to-date sales, so wanted to do a sumif
statement at the end. I have the current week number listed on a separate
worksheet.

wk # 1 2 through to 52
code wk01 wk02 wk52 YTD Total
Shop A AX1650 129 20 0
Shop B AX3502 0 0 0

I thought the formula for Shop A would look like:
=SUMIF(C1:BB1,"<='Weekly Summary'!$G$1",C3:BB3)

However, I keep getting 0 as the answer. It would also be great to just
have it recognize wk01 or wk15 and let me do the less than/equal to the
second row, but don't know if the "wk" stops that.

You can use 1 to 52 in the cells in the header, while you format the cells to
look like wk01, ... Use a custom notation.

Then, you can calculate totals using numbers.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Anne" wrote:

Every week I have to calculate year-to-date sales, so wanted to do a sumif
statement at the end. I have the current week number listed on a separate
worksheet.

wk # 1 2 through to 52
code wk01 wk02 wk52 YTD Total
Shop A AX1650 129 20 0
Shop B AX3502 0 0 0

I thought the formula for Shop A would look like:
=SUMIF(C1:BB1,"<='Weekly Summary'!$G$1",C3:BB3)

However, I keep getting 0 as the answer. It would also be great to just
have it recognize wk01 or wk15 and let me do the less than/equal to the
second row, but don't know if the "wk" stops that.

Why can't you just do:

=SUM(C3:BB3)

?

Hope this helps.

Pete

On Jan 10, 10:36*am, Anne wrote:
Every week I have to calculate year-to-date sales, so wanted to do a sumif
statement at the end. *I have the current week number listed on a separate
worksheet.

* * * * wk # * *1 * * * 2 * * * through to 52 *
* * * * code * *wk01 * *wk02 * *wk52 * *YTD Total
Shop A *AX1650 *129 * * 20 * * *0 * * *
Shop B *AX3502 *0 * * * 0 * * * 0 * * *

I thought the formula for Shop A would look like:
=SUMIF(C1:BB1,"<='Weekly Summary'!$G$1",C3:BB3)

However, I keep getting 0 as the answer. *It would also be great to just
have it recognize wk01 or wk15 and let me do the less than/equal to the
second row, but don't know if the "wk" stops that.

I guess he has numbers in the cells before the date arrives?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Pete_UK" wrote in message
...
Why can't you just do:

=SUM(C3:BB3)

?

Hope this helps.

Pete

On Jan 10, 10:36 am, Anne wrote:
Every week I have to calculate year-to-date sales, so wanted to do a sumif
statement at the end. I have the current week number listed on a separate
worksheet.

wk # 1 2 through to 52
code wk01 wk02 wk52 YTD Total
Shop A AX1650 129 20 0
Shop B AX3502 0 0 0

I thought the formula for Shop A would look like:
=SUMIF(C1:BB1,"<='Weekly Summary'!$G$1",C3:BB3)

However, I keep getting 0 as the answer. It would also be great to just
have it recognize wk01 or wk15 and let me do the less than/equal to the
second row, but don't know if the "wk" stops that.

It's actually pulling sales from last year. I'm using it to compare how we
have done this year to the same time last year, so I only want up until the
current week we are in. There would be 52 weeks worth of sales.



"Pete_UK" wrote:

Why can't you just do:

=SUM(C3:BB3)

?

Hope this helps.

Pete

On Jan 10, 10:36 am, Anne wrote:
Every week I have to calculate year-to-date sales, so wanted to do a sumif
statement at the end. I have the current week number listed on a separate
worksheet.

wk # 1 2 through to 52
code wk01 wk02 wk52 YTD Total
Shop A AX1650 129 20 0
Shop B AX3502 0 0 0

I thought the formula for Shop A would look like:
=SUMIF(C1:BB1,"<='Weekly Summary'!$G$1",C3:BB3)

However, I keep getting 0 as the answer. It would also be great to just
have it recognize wk01 or wk15 and let me do the less than/equal to the
second row, but don't know if the "wk" stops that.

=SUMIF(C1:BB1,"<="&'Weekly Summary'!$G$1,C3:BB3)


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Anne" wrote in message
...
Every week I have to calculate year-to-date sales, so wanted to do a sumif
statement at the end. I have the current week number listed on a separate
worksheet.

wk # 1 2 through to 52
code wk01 wk02 wk52 YTD Total
Shop A AX1650 129 20 0
Shop B AX3502 0 0 0

I thought the formula for Shop A would look like:
=SUMIF(C1:BB1,"<='Weekly Summary'!$G$1",C3:BB3)

However, I keep getting 0 as the answer. It would also be great to just
have it recognize wk01 or wk15 and let me do the less than/equal to the
second row, but don't know if the "wk" stops that.

Thanks Bob - that fixed it perfectly!

Cheers
Anne


"Bob Phillips" wrote:

=SUMIF(C1:BB1,"<="&'Weekly Summary'!$G$1,C3:BB3)


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Anne" wrote in message
...
Every week I have to calculate year-to-date sales, so wanted to do a sumif
statement at the end. I have the current week number listed on a separate
worksheet.

wk # 1 2 through to 52
code wk01 wk02 wk52 YTD Total
Shop A AX1650 129 20 0
Shop B AX3502 0 0 0

I thought the formula for Shop A would look like:
=SUMIF(C1:BB1,"<='Weekly Summary'!$G$1",C3:BB3)

However, I keep getting 0 as the answer. It would also be great to just
have it recognize wk01 or wk15 and let me do the less than/equal to the
second row, but don't know if the "wk" stops that.