evaluate data by week number
 

I have a forecasting spreadsheet I need to create from a data export. The
export gives me a range of data with dates, stages, and dollar amounts. What
I need to report is the weekly dollar amount by stage for any given month. I
found with the WEEKNUM formula, I can figure out what week in the month I
need. So I created spaces for July week1,2,3,4,5. They have corresponding
week values of 27,28,29,30,31, & 32(resulting from the WEEKNUM formula)
horizontally, and stages 1 through 6 vertically under each section. I can use
a SUMIF function to breakout by stage for all data, but I need to go one more
level down. Due to the nature of this worksheet, I need to perform the
WEEKNUM function or something of that nature during the summary
calculation(It cannot be a part of the exported data)

I have tried using the WEEKNUM(A3,1) to get the week number, so I know it
works, I have used SUMIF('Sheet 2'!A:A, Stage 1,'Sheet 2'!C:C) to get the Sum
of all data in Stage 1. I need to evaluate the same were all data is in Stage
1 AND Week 27, but when you do a range query with the WEEKNUM formula it
errors out. Can anyone help?

Hi

One way is to use a helper column with =WEEKNUM(Cell) in for each row. You
can then use something like this:
=SUMPRODUCT(--(A2:A50="Stage 1")*(D2:D50=28),(C2:C50))
with your Stages in column A, your Weeknum in D and your value in C

Hope this helps.
Andy

"Steve G" <Steve wrote in message
...
I have a forecasting spreadsheet I need to create from a data export. The
export gives me a range of data with dates, stages, and dollar amounts.
What
I need to report is the weekly dollar amount by stage for any given month.
I
found with the WEEKNUM formula, I can figure out what week in the month I
need. So I created spaces for July week1,2,3,4,5. They have corresponding
week values of 27,28,29,30,31, & 32(resulting from the WEEKNUM formula)
horizontally, and stages 1 through 6 vertically under each section. I can
use
a SUMIF function to breakout by stage for all data, but I need to go one
more
level down. Due to the nature of this worksheet, I need to perform the
WEEKNUM function or something of that nature during the summary
calculation(It cannot be a part of the exported data)

I have tried using the WEEKNUM(A3,1) to get the week number, so I know it
works, I have used SUMIF('Sheet 2'!A:A, Stage 1,'Sheet 2'!C:C) to get the
Sum
of all data in Stage 1. I need to evaluate the same were all data is in
Stage
1 AND Week 27, but when you do a range query with the WEEKNUM formula it
errors out. Can anyone help?