I would really appreciate a bit of help in calculating a formula.

I have a spreadsheet which is summarising data from another file.

The source file for the data is set out as below

col E row 2 contains the Part number
col G to AP row 3 contains dates,ie, 02-Feb,09-Feb,06-Mar,19-Apr
col G to AP row 4 contains number of units required

The next Part number begins on col E row 9
col G to AP row 10 contains dates,ie, 02-Feb,09-Feb,06-Mar,19-Apr
col G to AP row 11 contains number of units required

and so on.

My summary file has the Part number in col A

In col D I want a formula to look up the part number in col A and sum the
qty of units required for Feb,March etc from the source file.

I have tried vlookup and sumproduct but I cant get it to work, please help
as it's doing me head in, been trying for hours.

Thanks
Winnie

Bit complicated but possible. Add new worksheet to ur source data file, fill
it according my suggestion and then move it to ur summarizing file (just if u
want to avoid retyping the path in the formula I am giving to you). I assume
source data are placed on sheet called List2, my new sheet with formula u
wish is on sheet called List1. In List1, insert the following to the cells:
a1: one of ur part no (create data validation list for all of ur part
numbers, you may later change it, by adding new lines for each part number
but do it step by step)
c2: 1-jan-09 (as number!)
c3: 1-feb-09 (as number!, continue to c13 with following month)

in D column you will insert formula u were looking for (assuming part number
in column E, dates 1 row bellow, quantities beneath in column G to AP, on
List2).
Insert the formula to D2 (List1):


=sum((month(C2)=month(offset(indirect(address(matc h($A$2,List2!$E$1:$E$100,0),5,1,1,"List2")),1,2,1, 36)))*offset(indirect(address(match($A$2,List2!$E$ 1:$E$100,0),5,1,1,"List2")),2,2,1,36))
then press ctrl+shift+enter (array formula), hold it and copy by dragging
down to each month.

I was as precise as possible and it should work for the whole year. Do not
ask me to explain the formula, as I said, bit complicated. I assume data in
List2 are in rows 2 to 100 (row 1 is header row). 36 is the number of columns
from G to AP.

Click yes if this helps.


"winnie123" wrote:

I would really appreciate a bit of help in calculating a formula.

I have a spreadsheet which is summarising data from another file.

The source file for the data is set out as below

col E row 2 contains the Part number
col G to AP row 3 contains dates,ie, 02-Feb,09-Feb,06-Mar,19-Apr
col G to AP row 4 contains number of units required

The next Part number begins on col E row 9
col G to AP row 10 contains dates,ie, 02-Feb,09-Feb,06-Mar,19-Apr
col G to AP row 11 contains number of units required

and so on.

My summary file has the Part number in col A

In col D I want a formula to look up the part number in col A and sum the
qty of units required for Feb,March etc from the source file.

I have tried vlookup and sumproduct but I cant get it to work, please help
as it's doing me head in, been trying for hours.

Thanks
Winnie

Thankyou very much, it does the job. Great

Winnie

"Alojz" wrote:

Bit complicated but possible. Add new worksheet to ur source data file, fill
it according my suggestion and then move it to ur summarizing file (just if u
want to avoid retyping the path in the formula I am giving to you). I assume
source data are placed on sheet called List2, my new sheet with formula u
wish is on sheet called List1. In List1, insert the following to the cells:
a1: one of ur part no (create data validation list for all of ur part
numbers, you may later change it, by adding new lines for each part number
but do it step by step)
c2: 1-jan-09 (as number!)
c3: 1-feb-09 (as number!, continue to c13 with following month)

in D column you will insert formula u were looking for (assuming part number
in column E, dates 1 row bellow, quantities beneath in column G to AP, on
List2).
Insert the formula to D2 (List1):


=sum((month(C2)=month(offset(indirect(address(matc h($A$2,List2!$E$1:$E$100,0),5,1,1,"List2")),1,2,1, 36)))*offset(indirect(address(match($A$2,List2!$E$ 1:$E$100,0),5,1,1,"List2")),2,2,1,36))
then press ctrl+shift+enter (array formula), hold it and copy by dragging
down to each month.

I was as precise as possible and it should work for the whole year. Do not
ask me to explain the formula, as I said, bit complicated. I assume data in
List2 are in rows 2 to 100 (row 1 is header row). 36 is the number of columns
from G to AP.

Click yes if this helps.


"winnie123" wrote:

I would really appreciate a bit of help in calculating a formula.

I have a spreadsheet which is summarising data from another file.

The source file for the data is set out as below

col E row 2 contains the Part number
col G to AP row 3 contains dates,ie, 02-Feb,09-Feb,06-Mar,19-Apr
col G to AP row 4 contains number of units required

The next Part number begins on col E row 9
col G to AP row 10 contains dates,ie, 02-Feb,09-Feb,06-Mar,19-Apr
col G to AP row 11 contains number of units required

and so on.

My summary file has the Part number in col A

In col D I want a formula to look up the part number in col A and sum the
qty of units required for Feb,March etc from the source file.

I have tried vlookup and sumproduct but I cant get it to work, please help
as it's doing me head in, been trying for hours.

Thanks
Winnie

Glad to hear it, just be aware that in the formula, for each part number
there is only one row for quantity assigned and one for date of requirement,
if u use more than one row, formula has to be even more complex!!

"winnie123" wrote:

Thankyou very much, it does the job. Great

Winnie

"Alojz" wrote:

Bit complicated but possible. Add new worksheet to ur source data file, fill
it according my suggestion and then move it to ur summarizing file (just if u
want to avoid retyping the path in the formula I am giving to you). I assume
source data are placed on sheet called List2, my new sheet with formula u
wish is on sheet called List1. In List1, insert the following to the cells:
a1: one of ur part no (create data validation list for all of ur part
numbers, you may later change it, by adding new lines for each part number
but do it step by step)
c2: 1-jan-09 (as number!)
c3: 1-feb-09 (as number!, continue to c13 with following month)

in D column you will insert formula u were looking for (assuming part number
in column E, dates 1 row bellow, quantities beneath in column G to AP, on
List2).
Insert the formula to D2 (List1):


=sum((month(C2)=month(offset(indirect(address(matc h($A$2,List2!$E$1:$E$100,0),5,1,1,"List2")),1,2,1, 36)))*offset(indirect(address(match($A$2,List2!$E$ 1:$E$100,0),5,1,1,"List2")),2,2,1,36))
then press ctrl+shift+enter (array formula), hold it and copy by dragging
down to each month.

I was as precise as possible and it should work for the whole year. Do not
ask me to explain the formula, as I said, bit complicated. I assume data in
List2 are in rows 2 to 100 (row 1 is header row). 36 is the number of columns
from G to AP.

Click yes if this helps.


"winnie123" wrote:

I would really appreciate a bit of help in calculating a formula.

I have a spreadsheet which is summarising data from another file.

The source file for the data is set out as below

col E row 2 contains the Part number
col G to AP row 3 contains dates,ie, 02-Feb,09-Feb,06-Mar,19-Apr
col G to AP row 4 contains number of units required

The next Part number begins on col E row 9
col G to AP row 10 contains dates,ie, 02-Feb,09-Feb,06-Mar,19-Apr
col G to AP row 11 contains number of units required

and so on.

My summary file has the Part number in col A

In col D I want a formula to look up the part number in col A and sum the
qty of units required for Feb,March etc from the source file.

I have tried vlookup and sumproduct but I cant get it to work, please help
as it's doing me head in, been trying for hours.

Thanks
Winnie