Any better way to do this?

[Guanjin Peter]

in each cell =SUMPRODUCT(--(db!$A$2:$A26=$B$1),--(db!$F$2:$F26=C$5),--(db!$G$2:$G26=C$6),--(db!$E$2:$E26=$A7),--(db!$D$2:$D26=$B7),db!$H$2:$H26)

is suppose to return values either 1,2 or 3

if it is 1, the cell value will be KG
if it is 2, the cell value will be BAG
if it is 3, the cell value will be PKT


VLOOKUP WON't work for me because it returns >>><TABLE style="WIDTH: 73pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=97 border=0 x:str><COLGROUP><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><TBODY><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 73pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 19.5pt; BACKGROUND-COLOR: transparent" width=97 height=26 x:err="#NAME?">#NAME?</TD></TR></TBODY></TABLE>
after go to each cell then press 'enter' key then it returns
1(or 2 or 3)

Unless i got a way to refresh the page....any idea guys?

protoV5.xls
	I	J	K	L
1	uomCode	Uom
2	1	KG
3	2	PKT
4	1	KG
5	3	BAG
6	1	KG
7	2	PKT
8	1	KG
9	3	BAG
10	2	PKT
11	3	BAG
12	2	PKT
13	1	KG
14	2	PKT
15	3	BAG
16	2	PKT
17	2	PKT
18	1	KG
19	2	PKT
20	2	PKT
21	3	BAG
22	1	KG
23	3	BAG
24	2	PKT
db

 

[Fazza]

Peter,

Looks like it might be OK. I keep thinking along the lines of my comments above. The spreadsheet structure is leading to unnecessarily complex formulas. Suggest you 'sit back' and revise the structure - it could be so much easier. (I'm referring to the whole spreadsheet and various other threads, not just this specific thread.)

regards, Fazza

 

[Stormseed]

=IF(OR(SUMPRODUCT(--(db!$A$2:$A57=$B$1),--(db!$F$2:$F57=C$5),--(db!$G$2:$G57=C$6),--(db!$E$2:$E57=$A7),--(db!$D$2:$D57=$B7),db!$J$2:$J57)={1,2,3}),CHOOSE(SUMPRODUCT(--(db!$A$2:$A57=$B$1),--(db!$F$2:$F57=C$5),--(db!$G$2:$G57=C$6),--(db!$E$2:$E57=$A7),--(db!$D$2:$D57=$B7),db!$J$2:$J57),"KG","PKT","BAG"),"Blank")

Shall I give you a suggestion on the usage of this formula ? I wanted to share something which I learnt yesterday regarding this formula.

 

[Guanjin Peter]

please advise.

 

[Aladin Akyurek]

please advise.

Try to state what reply or the replier you are addressing...

 

[Stormseed]

Well, I see that you have made use of the OR() function in SUMPRODUCT(). If I have understood correctly, then you should use the + operator instead of an OR() in the formula because Excel wiill first calculate the value for this 'OR()' boolean across all the conditions and will take the final result of this boolean output while calculating SUMPRODUCT(), thereby creating a discrepancy. It is better to be cautious.

I do think about it this way and I really don't know if I have understood this correctly, as PGC has explained it to me. We do have masters of excel around and I am sure they would correct my statements incase I am wrong and I guess it would help me too.

Look for PGC'c post number 4 & 7 in which he is addressing me: http://www.mrexcel.com/forum/showthread.php?t=323007

 

[Guanjin Peter]

thank you. I don't really understand the depths of what you meant, but I'll take a look.