Hi fellows,
Having trouble trying to make a formula work. Could you help me?
Attached workbook is simple and self-explanatory.
*If possible, do not use an auxiliar row to make the desired formula to work.
Thank you.
Printable View
Hi fellows,
Having trouble trying to make a formula work. Could you help me?
Attached workbook is simple and self-explanatory.
*If possible, do not use an auxiliar row to make the desired formula to work.
Thank you.
in H12 you can put
you can then drag that formula downCode:=SUM(VLOOKUP(C$15,$B$6:$C$7,2)*C12,VLOOKUP(D$15,$B$6:$C$7,2)*D12,VLOOKUP(E$15,$B$6:$C$7,2)*E12)
there is probably a way to do this as an array formula but I'm not sure how....
Here's one way
Code:=SUMPRODUCT(C12:E12,N(OFFSET($C$6,MATCH($C$15:$E$15,$B$6:$B$7,0)-1,0)))
BTW, that would have been a nice one to put in our new SUMPRODUCT sub-forum
I think so too!
Thank you guys, and thanks for moving thread.
I chose xld's formula because my real table is bigger and has a lot of "Quantity" columns.
xld: why does the formula doesn't work if I take the 'N' function?
One more question:
Occasionaly, some cell of row 15 will be at blank. If this happens, I get an error from the formula. Is there any way to fix it?
I tried using IF/ISERROR and also IF/C15:E15<>"" as an array formula with no success.
Edit: Assume that when that cell in row 15 is blank, the value to multiply the elements in the column is 0.
How about a simple workaround; force a substitute if a letter is deleted.
I did it!
The array formula is:
The only bad thing is (I think) that the CPU cost of this formula is higher than if I used a hidden auxiliar row, like rule #9 at this link: http://www.ozgrid.com/forum/showthread.php?t=76234, or like a hidden LOOKUPV at row 16 bringing the desired letter value.Code:=SUMPRODUCT(C12:E12,IF(ISERROR(N(OFFSET($C$6,MATCH($C$15:$E$15,$B$6:$B$7,0)-1,0))),0,N(OFFSET($C$6,MATCH($C$15:$E$15,$B$6:$B$7,0)-1,0))))
I got one more challenge... the column at my database where I'm using this formula is filled by merged cells, and Excel does not accept array formulas in merged cells.
I managed to do a better formula than the one above (it's an array formula too):
=SUMPRODUCT(C12:E12,IF($C$15:$E$15="",0,N(OFFSET($C$6,MATCH($C$15:$E$15,$B$ 6:$B$7,0)-1,0))))
As for the problem of the merged cells array formula, I noticed that merged cells can have an array formula by doing this:
1 - Unmerge the cells where you want to put the formula;
2 - Put the array formula on the single top cell;
3 - Merge again as desired;
4 - Copy the formula to the others cells.
If you need to CSE it, there is no point in SUMPRODUCT, you might just as well SUM it
Code:=SUM((C12:E12)*(IF($C$15:$E$15="",0,N(OFFSET($C$6,MATCH($C$15:$E$15,$B$6:$B$7,0)-1,0)))))
Don't use merged cells, they are more trouble than they are worth.Quote:
Originally Posted by Benzadeus
Because OFFSET returns cell references, we need the values, so with use N to force them to values.Quote:
Originally Posted by Benzadeus
Could not convince my boss about that.Quote:
Originally Posted by xld
I'm sorry, but what is CSE?Quote:
Originally Posted by xld
Anyway, thanks for all explanation.
*Your SUMPRODUCT's www.xldynamic.com/source/xld.SUMPRODUCT.html is offline.
CSE is Control-Shift-Enter.Quote:
Originally Posted by Benzadeus
Using Centre Across Selection usually achieves the same appearance without causing VBA problems. At work, I have reassigned the button to avoid the merged cells situation.Quote:
Originally Posted by Benzadeus