Help please with formula

[winnie123]

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

 

[Roger Govier]

Hi Winnie


With Part number required in B1
Try
=SUMPRODUCT((E2:E100=B1)*(MOD(ROW(E2:E100),7)=0+2)
*(MONTH(G3:AP101)=2)*(G3:AP101<>"")*(MOD(ROW(G3:AP101),7)=0+3)
*(MOD(ROW(G4:AP102),7)=0+4)*G4:AP104)

Adjust ranges to suit the depth of your data.

 

[Shane Devenshire]

Hi,

Do you want the sum of all the months from column G to AP? On the summary
sheet are the part number spaced apart like they are on the data sheet or do
they start in say A2 and continue down on eahc line?

It would be helpful if you show us some sample data as it is laid out in the
Data sheet and a same of what you want the summary sheet to look like base on
the sample data you show us.