Can i add a multiplier in a Sumif?

Here is the Formula. =SUMIF(***!$F$6:$F$951,B16,***!$G$6:$G$951)

excel screen shot.PNG
This is the page it is referencing to.

PO screen shot.PNG
This is where the formula is. It is in "I16"

What I want to do is Take this and add in the multiplier from column "H" in the "***" page. I need to change the name of that tab, I am not trying to be foul. I just use that short for assembly. It looks bad, but usually, I'm the only one that sees it.

Basicly, I want to take the material qnty and multiply it by the multiplier and put that in the box on the po page that cumulates the material.
If need be I can put the actual workbook on here. But maybe someone can guide me through how to do the formula. Thanks for any help, in advance!

Any chance you could post a sample workbook rather than screen shots?

st2.25.14.xlsx See if this works?

Yup that works. Now, can you tell me an example of an expected result, where that result should be and how you arrived at that value?

Ok, here goes.

On the "assembly" page. I zero in on a part in need from the drop down lists. I then put in a qnty required for that assembly(column G). I then want to put in a multiplier for multiple assemblies(Column H). For instance, I can build one complete assembly, but if I need the assembly for 10 tanks, the I can put in a multiplier for that. On the "1st PO" page, in column b it retrieves the part name from the "Assembly" page. In column F retrieves the Part number from, the "data" page. Its hidden, you can unhide if needed. Column I is where the Formula is that I need to modify. right now it just accumulates from the Qnty row on "assembly page. but I need it to use the multiplier as well, from the assembly page. Row I as of now accumulates like parts in one line and adds all the qnty's together for that part. I tend to use a lot of parts over and over for different assemblies. So I, we "Excel Forum" made it to accumulate like parts. For ordering. This is about the last thing, I need to figure out. And this thing will save me some headaches! Thanks for any ideas.

So you want to multiply the quantity calculated in column I on the 1st PO sheet by the relevant multiplier in column H on the Assembly sheet?
Meaning "1-1/2" T" would be 1 * 10 = 10 and "3/4" GRC" would be 10 * 10 = 100?

Have I understood correctly? And if so, where do you want that total? Column J on the 1st PO sheet??

Column "I" on the 1st po sheet. If possible I just want to amend the formula to include the respective multiplier. Can that be done?

column "J" is just for me to put in the amount I want to order, in case I have stock on hand.

Try this in I16 and copy down:

=IFERROR(SUMIF(***!$F$6:$F$951,B16,***!$G$6:$G$951)*INDEX(***!H:H,MATCH(***!F7,'1st PO'!B:B,0)),"")

It sort of works. But the math the comes out wrong. Should the references you added at the end be absolute?

Not if you're copying down only. If you're copying to the right then you'll need to make the column reference absolute.

Can you explain what's not working about it? Perhaps repost your attachment and highlight what's not working and why?

monkey.xlsx When I add in another "like" part on the assembly "Row 32". it adds up wrong, it should come up to 130 for the 3/4" grc in "I18" of the "1st PO" page.

Not sure if I'm completely missing the point here..

There is nothing in row 32 of the Assembly sheet and the formulas have not been updated to the one I posted above, so still not sure where you're having the issue.

monkey.xlsx Sorry, that was my fault. I must have not saved before I uploaded. Here it is again.

What about this in I16 and copy down?

=IFERROR(SUMIF(***!$F$6:$F$951,B16,***!$G$6:$G$951)*SUMIF(***!$F$6:$F$951,B16,***!$H$6:$H$951),"")

Although if my understanding is correct, the figure in I18 should be 260 rather than 130.

On row 9 of the assembly page it should be "G9" x "H9" Which should total 100.
On row 32 of the assembly page it should be "G32" x "H32" which should total 30.
That should accumulate in at "I18" of the 1st PO page as 130.

Because they are the same part. Instead of having 2 lines for the same part.

Let me try that formula, and see what it does.

It getting closer, its just double the actual amount. Excel make me pull my hair out! But, I'm learning! LOL Honestly, all that I do know, has been learned mostly on this forum. Its an awesome resource.

monkey2.xlsx here is the new one with that formula in it. Just so you can see, if you want.

I'll be back shortly, I have to go look at a PV job real quick. So if I don't respond for a little while, it doesn't mean I have lost interest. Thanks for your time so far. It is appreciated.

Third time lucky... How about this one in cell I16 and copy down?

=IFERROR((SUMIF(***!$F$6:$F$951,B16,***!$G$6:$G$951)*SUMIF(***!$F$6:$F$951,B16,***!$H$6:$H$951))/COUNTIF(***!F:F,B16),"")

monkey2.xlsx
Ok, I'm back. I inserted that one. and, it is calculating right for that particular line. But I added another duplicate part to check it. and it calculated it wrong. Look at rows 10 and 33 on the assembly page. They should accumulate to 16, but it returned 24 on "I19" of the 1st po page. Something is still not quite right.

Now if I can just solve this one. Any help would be appreciated. BaddlySpelledBouy has gotten me close. But I would welcome any other suggestions as well. He's probably out like a light. I think he's in England, probably night, night there.

monkey2 macro enabled.xlsm

just to clarify further. The line items Highlighted on the "Assembly" page, should accumulate on the "1st PO" page in the same highlighted color line. Highlights are just for reference, to show what I am trying to do. It is just doing the math wrong for some reason. The formula is in the "1st PO" page, Column "I".

Originally Posted by FDibbins

Perhaps try this?

=SUMPRODUCT((***!$F$7:$F$33='1st PO'!B16)*(***!$G$7:$G$33)*(***!$H$7:$H$33))

Adjust ranges as needed

That seems to work, guess there is a 100 ways to skin a cat! Would it be possible to break that equation down for me, as to what it does? That way, I will understand a little better. And, hopefully learn something. Hopefully! lOL