Sum if or some sort of match formula

[JavyD]

Hello everyone,

I'm wondering if I could use some sort of Sumif or Sumproduct formula for
this with a few if's. Let me know if you agree and if you could have a
helpfull hint. I was going to do this with access, but perhaps its best with
Excel

I'am so stumped.

I have a spreadsheet with item numbers, and the parts that are used in the
items like so
item plastic balls staples
12345 2 4
67890 0 5
And theres many more parts,

and then I have a monthly projected demand by item spreadsheet, with the
item number and the monthly demand like so
item jan feb
12345 20 22
67890 2 15

So now, how do I get a query done that will bring back the parts in a row,
then months as the column headings with the quantities that I will need for
each month once the query figures out that a part belongs to an item then it
multiples the quantity that item needs of a part times the fcst for that
month. I hope someone can help.

[Bob Phillips]

Where is the relationship between the parts in table 1 and the months in
table 2 defined.

Perhaps work through a full example?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JavyD" <JavyD@discussions.microsoft.com> wrote in message
news:D272536A-87DC-4F15-B714-9CB015FE5FDF@microsoft.com...
> Hello everyone,
>
> I'm wondering if I could use some sort of Sumif or Sumproduct formula for
> this with a few if's. Let me know if you agree and if you could have a
> helpfull hint. I was going to do this with access, but perhaps its best
with
> Excel
>
> I'am so stumped.
>
> I have a spreadsheet with item numbers, and the parts that are used in the
> items like so
> item plastic balls staples
> 12345 2 4
> 67890 0 5
> And theres many more parts,
>
> and then I have a monthly projected demand by item spreadsheet, with the
> item number and the monthly demand like so
> item jan feb
> 12345 20 22
> 67890 2 15
>
> So now, how do I get a query done that will bring back the parts in a row,
> then months as the column headings with the quantities that I will need
for
> each month once the query figures out that a part belongs to an item then
it
> multiples the quantity that item needs of a part times the fcst for that
> month. I hope someone can help.
>
>

[JavyD]

parts in table one looks like t his

item part nbr 1

12345 6 the six is the quantity of pcs of part
nbr 1 that are in item 12345

And then that item is in table two with quantities by month and months are
the headings.

"Bob Phillips" wrote:

> Where is the relationship between the parts in table 1 and the months in
> table 2 defined.
>
> Perhaps work through a full example?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "JavyD" <JavyD@discussions.microsoft.com> wrote in message
> news:D272536A-87DC-4F15-B714-9CB015FE5FDF@microsoft.com...
> > Hello everyone,
> >
> > I'm wondering if I could use some sort of Sumif or Sumproduct formula for
> > this with a few if's. Let me know if you agree and if you could have a
> > helpfull hint. I was going to do this with access, but perhaps its best
> with
> > Excel
> >
> > I'am so stumped.
> >
> > I have a spreadsheet with item numbers, and the parts that are used in the
> > items like so
> > item plastic balls staples
> > 12345 2 4
> > 67890 0 5
> > And theres many more parts,
> >
> > and then I have a monthly projected demand by item spreadsheet, with the
> > item number and the monthly demand like so
> > item jan feb
> > 12345 20 22
> > 67890 2 15
> >
> > So now, how do I get a query done that will bring back the parts in a row,
> > then months as the column headings with the quantities that I will need
> for
> > each month once the query figures out that a part belongs to an item then
> it
> > multiples the quantity that item needs of a part times the fcst for that
> > month. I hope someone can help.
> >
> >
>
>
>

[Bob Phillips]

JavyD,

All of that I got, but how would we know for instance what number goes in
Jan, what goes in Feb, etc.? And do we add all parts for the month (when you
tell us how to identify it), or just some?

As I said, a worked example may be the best explanation.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JavyD" <JavyD@discussions.microsoft.com> wrote in message
news:6B7E616B-8F2C-4EEC-914C-3A57C4C08CEE@microsoft.com...
> parts in table one looks like t his
>
> item part nbr 1
>
> 12345 6 the six is the quantity of pcs of
part
> nbr 1 that are in item 12345
>
> And then that item is in table two with quantities by month and months are
> the headings.
>
> "Bob Phillips" wrote:
>
> > Where is the relationship between the parts in table 1 and the months in
> > table 2 defined.
> >
> > Perhaps work through a full example?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "JavyD" <JavyD@discussions.microsoft.com> wrote in message
> > news:D272536A-87DC-4F15-B714-9CB015FE5FDF@microsoft.com...
> > > Hello everyone,
> > >
> > > I'm wondering if I could use some sort of Sumif or Sumproduct formula
for
> > > this with a few if's. Let me know if you agree and if you could have
a
> > > helpfull hint. I was going to do this with access, but perhaps its
best
> > with
> > > Excel
> > >
> > > I'am so stumped.
> > >
> > > I have a spreadsheet with item numbers, and the parts that are used in
the
> > > items like so
> > > item plastic balls staples
> > > 12345 2 4
> > > 67890 0 5
> > > And theres many more parts,
> > >
> > > and then I have a monthly projected demand by item spreadsheet, with
the
> > > item number and the monthly demand like so
> > > item jan feb
> > > 12345 20 22
> > > 67890 2 15
> > >
> > > So now, how do I get a query done that will bring back the parts in a
row,
> > > then months as the column headings with the quantities that I will
need
> > for
> > > each month once the query figures out that a part belongs to an item
then
> > it
> > > multiples the quantity that item needs of a part times the fcst for
that
> > > month. I hope someone can help.
> > >
> > >
> >
> >
> >