Sumproduct on same column, mod?, sumproduct?

[lilianneisP]

I have searched google for couple months (seriously) and still haven't got the answer yet for the problem I have.
I am making a simulation chart with multiple vendors and multiple items as below.
I input my simulation in yello highlight cells and rest of the chart needs to have formulas to calculate.

Where I am stuck is that sumarize unit cost per item or per vendor. (Grey, light blue section)
To do this, I need to multiply unit cost * Qty per each item and sum by specific item or vendor. I came across SUMPRODUT and MOD formula and try to use them but no luck so far.

I have used
SUMPRODUCT(--(B3:B20="A")*(D3:D20="UNIT COT)*(D3:D20="QTY"),(E3:E20)
SUMPRODUCT(E3:E18*E5:E20*MOD(ROW(E5:E20),3)=0,MOD(ROW(E5:E20),3)=2)

can anyone help!???

 

[David Evans]

upload the actual file - you'll get a faster answer ...

 

[lilianneisP]

uploading the file. THank you for the suggestion!

 

[eibi]

If I correctly understand, I think you can paste the following formula in E21 and copy in to the other cells in the grey section...

=SUMPRODUCT(($C$3:$C$18=C21)*($E$3:$E$18),$E$5:$E$20)

and modify slightly and paste the following into E41 with copy and paste in the blue section:

=SUMPRODUCT(($B$3:$B$18=B41)*($E$3:$E$18),$E$5:$E$20)

Does this provide the desired output?

See attached...

------
PS. I notice that in your sample file, the formula that results in #N/A has a multiplier of 1.1 -- I didn't see that in your original post...

 

[lilianneisP]

Yes! It did work! Thank you so much!
But if there is any zero or blank value on simulation, the formula would turn to #VALUE!.
How should I modify your formula?

 

[eibi]

Can you be more specific about what you are doing to create the error?

If I enter a 0 value or delete the contents in the yellow cells, my orange cells still calculate without problems.

I only generate a #VALUE error when I enter text into one of the yellow cells.

 

[lilianneisP]

Yes, it does work fine on your file.
For some reason the one I am working on as attached does not corretly calculate the blank/zero simulated numbers.

 

[eibi]

In order to focus the discussion, let's look at the error in cell G284.

It is not created by the values in the yellow cells -- but by a value in the white cells!

Your IFERROR formula in G271, G275, G279, G283 is returning an empty value: ""

This is binding up the SUMPRODUCT, which needs a value for its operation.

There are two ways to 'fix' this.

Option 1, use an alternate IFERROR result in the Qty% rows...

Option 2, [too ugly to discuss in public...]

**don't do this -- I'm warning you -- ** -> Add an IFERROR inside your sumproduct formula and (I think you will have to) confirm as an array using Ctrl+Shift+Enter

=SUMPRODUCT(IFERROR(($E$260:$E$280=$E284)*($C$260:$C$280=$C284)*(G$260:G$280)*($F$260:$F$280="cost"),0),(G$262:G$282))​

My conscious is killing me because Option 2 isn't really what you need...It doesn't solve the real problem, just masks it.

It will pass the error, but **use with caution**, because it may actually ignore other important errors in your data, which isn't want you will ultimately want.

 

[lilianneisP]

I revised the IFERROR formula in G271, G275, G279, G283 returning to Zero instead of "". Works now!!
Thank you so much for your help!!