Not sure what formula or array to use

[Alexander]

I didn't know where to ask. I've attached an excel 2007 worksheet to
help show the problem. Please point me in the right direction if someone
could please.

1) I have a weekly forcast of mother parts. Weeks are on the top row.
MotherParts on on the left column. MotherParts might not always be in
the
same order, and they do repeat, but do not repeat in any order.
2) There are a set number of child parts for each type of mother part.
MotherPartA may have Xnumber of ChildPartM, and MotherPartB may have
Ynumber of ChildPartN and Znumber of ChildpartM. Childparts per mother
part does not change, it can be listed in a small table.
3) I'm needing a daily forcast of childparts.

I've tried {sum((range=criteria)*(range=criteria)*(data))} arrays, but

having a difficult time (a) matching the day to the weekly forcast and
(b)
multipling by the corrosponding number of child parts.
I've tried in Index(data,match(criteria,rang),match(criteria,range))
function, but it only returns the forcast from the 1st mother part it
sees,
and doesn't add together the mulitple mother part forecasts.

Thank you anyone for some direction, rather it be a suggestion for a
formula
or somewhere else to ask.


+-------------------------------------------------------------------+
|Filename: example.xlsx |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=230|
+-------------------------------------------------------------------+

 

[Bernard Liengme]

I have to wish to join CageCode so I cannot see your worksheet
Suggest you read up on SUMPRODUCT

Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
Debra Dalgleish
http://www.contextures.com/xlFunctions04.html#SumProduct

Then come back and give us a brief idea of how you worksheet looks

 

[NBVC]

I didn't know where to ask. I've attached an excel 2007 worksheet t
help show the problem. Please point me in the right direction if someon
could please.

1) I have a weekly forcast of mother parts. Weeks are on the top row.
MotherParts on on the left column. MotherParts might not always be i
the
same order, and they do repeat, but do not repeat in any order.
2) There are a set number of child parts for each type of mother part
MotherPartA may have Xnumber of ChildPartM, and MotherPartB may hav
Ynumber of ChildPartN and Znumber of ChildpartM. Childparts per mothe
part does not change, it can be listed in a small table.
3) I'm needing a daily forcast of childparts.

I've tried {sum((range=criteria)*(range=criteria)*(data))} arrays, bu

having a difficult time (a) matching the day to the weekly forcast an
(b)
multipling by the corrosponding number of child parts.
I've tried in Index(data,match(criteria,rang),match(criteria,range))
function, but it only returns the forcast from the 1st mother part i
sees,
and doesn't add together the mulitple mother part forecasts.

Thank you anyone for some direction, rather it be a suggestion for
formula
or somewhere else to ask.

Try:


Code
-------------------
=IF(LEFT(TEXT(C23,"ddd"))="S","",SUMPRODUCT(SUMIF($D$21:$D$22,$C$4:$C$12,$E$21:$E$22)*($D$3:$K$3=C24-WEEKDAY(C24,3)),$D$4:$K$12)/5
-------------------


copied acros

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com