Data Summary

[Ravi]

Hi,

I was asked to prepare a sheet which automatically sums data entered
in first sheet.

the scenario is like this

Sheet1 (data entered in to this sheet)
date--> 1 2 3 4 ......31
Item
x 3 4 2 6
y 10 9 2 3
z 2 3 3 4

Sheet2 (calculated data is posted here)
eg for Oct, firstweek has only 2 days 1 & 2. So values from dates 1
and 2 is added and posted in firstweek column.

Firstweek SecondWeek ThirdWeek FourthWeek Fifthweek
x 7
y 19
z 5


In the sheet2 depending on the current month the value from dates
should get added and displayed in appripriate weeks.

Is this kind of calculation possible in excel...?

i'm very new to excel programming....

Does no where to start off...

since i have very little time for doing this any help reg this ASAP
will be appreciated GREATLY.


Thanks in advance

Ravi

 

[Debra Dalgleish]

On Sheet 1, insert a row at the top of the sheet, and enter the week
number above each date

On Sheet 2, change the headings to numbers, instead of text

A B C D E F G
1 1 2 3 4 5 6
2 x
3 y
4 z

On sheet 2, cell B2, enter the formula:


=SUMPRODUCT((Sheet1!$A$3:$A$5=$A2)*(Sheet1!$B$1:$AF$1=B$1)*(Sheet1!$B$3:$AF$5))

Copy across to column G, and down to row 4