I'm having a problem getting this formula correct..
=IF(AND(B1="SF",B2:B100=N12),B1,IF(AND(C1="LF",C2:C100=N12),C1,))
The result returned is 0, It should return "LF"
Any help would be greatly appreciated...
Thanks...
Michael
I'm having a problem getting this formula correct..
=IF(AND(B1="SF",B2:B100=N12),B1,IF(AND(C1="LF",C2:C100=N12),C1,))
The result returned is 0, It should return "LF"
Any help would be greatly appreciated...
Thanks...
Michael
Does this work for you, distilled from the non-working formula you posted?
=IF(AND(B1="SF",ISNUMBER(MATCH(N12,B2:B100,0))),B1,IF(AND(C1="LF",ISNUMBER(MATCH(N12,C2:C100,0))),C1,"no value"))
Thank You Aladin..! Works Perfect...!
(I might have another one for you.. I'll post it back here if I do...)
Thanks
Michael
You are very welcome. Do not hesitate to come back whenever in need or otherwise.
Excel 2012 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Description | SF | LF | EA | ||
2 | 6in A Curb | 6439.682 | 201.151 | 1 | ||
3 | 4in Sidewalk | 100.585 | 91.782 | 1 | ||
4 | Lgt Standard | 0.401 | 14 | |||
5 | 4in Expansion Paper w_Chalk | 1.931 | 1 | |||
6 | 4in Expansion Paper w_Chalk | 1.888 | 1 | |||
7 | 4in Expansion Paper w_Chalk | 1.974 | 1 | |||
8 | 4in Expansion Paper w_Chalk | 2.180 | 1 | |||
9 | 4in Expansion Paper w_Chalk | 2.046 | 1 | |||
10 | 4in Expansion Paper w_Chalk | 1.943 | 1 | |||
Summary |
Excel 2012 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Description | SF | LF | EA | ||
2 | 6in Concrete Curb | 201.15 | ||||
3 | 4in Concrete Sidewalk | 100.59 | ||||
4 | Light Standard Foundation | 14 | ||||
5 | 4in Expansion Paper w_Chalk | 1.931 | ||||
6 | 4in Expansion Paper w_Chalk | 1.888 | ||||
7 | 4in Expansion Paper w_Chalk | 1.974 | ||||
8 | 4in Expansion Paper w_Chalk | 2.18 | ||||
9 | 4in Expansion Paper w_Chalk | 2.046 | ||||
10 | 4in Expansion Paper w_Chalk | 1.943 | ||||
Summary |
Excel 2012 | |||||
---|---|---|---|---|---|
A | B | C | |||
12 | Description | Qty | Unit | ||
13 | 6in Concrete Curb | 201.15 | LF | ||
14 | 4in Concrete Sidewalk | 100.59 | SF | ||
15 | Light Standard Foundation | 14 | EA | ||
16 | 4in Expansion Paper w_Chalk | 11.962 | LF | ||
Summary |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B15 | =SUMIF($A:$A,A15,$D:$D) | |
B16 | =SUMIF($A:$A,A16,$C:$C) |
=SUMPRODUCT(INDEX($B$2:$D$10,0,MATCH($C13,$B$1:$D$1,0)),
--(SUBSTITUTE($A$2:$A$10,CHAR(160),"")=SUBSTITUTE($A13,CHAR(160),"")))
=LOOKUP(9.99999999999999E+307,INDEX($B$2:$D$10,MATCH(SUBSTITUTE($A13,CHAR(160),""),
SUBSTITUTE($A$2:$A$10,CHAR(160),""),0),0),$B$1:$D$1)
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]A[/td][td]B[/td][td]C[/td][td]D[/td][/tr][tr][td]1[/td][td]Description[/td][td]SF[/td][td]LF[/td][td]EA[/td][/tr]
[tr][td]2[/td][td]6in Concrete Curb[/td][td][/td][td]201.15[/td][td][/td][/tr]
[tr][td]3[/td][td]4in Concrete Sidewalk[/td][td]100.59[/td][td][/td][td][/td][/tr]
[tr][td]4[/td][td]Light Standard Foundation[/td][td][/td][td][/td][td]14[/td][/tr]
[tr][td]5[/td][td]4in Expansion Paper w_Chalk[/td][td][/td][td]1.931[/td][td][/td][/tr]
[tr][td]6[/td][td]4in Expansion Paper w_Chalk[/td][td][/td][td]1.888[/td][td][/td][/tr]
[tr][td]7[/td][td]4in Expansion Paper w_Chalk[/td][td][/td][td]1.974[/td][td][/td][/tr]
[tr][td]8[/td][td]4in Expansion Paper w_Chalk[/td][td][/td][td]2.18[/td][td][/td][/tr]
[tr][td]9[/td][td]4in Expansion Paper w_Chalk[/td][td][/td][td]2.046[/td][td][/td][/tr]
[tr][td]10[/td][td]4in Expansion Paper w_Chalk[/td][td][/td][td]1.943[/td][td][/td][/tr]
[tr][td]11[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]12[/td][td]Description[/td][td]Qty[/td][td]Unit[/td][td][/td][/tr]
[tr][td]13[/td][td]6in Concrete Curb[/td][td]201.15[/td][td]LF[/td][td][/td][/tr]
[tr][td]14[/td][td]4in Concrete Sidewalk[/td][td]100.59[/td][td]SF[/td][td][/td][/tr]
[tr][td]15[/td][td]Light Standard Foundation[/td][td]14[/td][td]EA[/td][td][/td][/tr]
[tr][td]16[/td][td]4in Expansion Paper w_Chalk[/td][td]11.962[/td][td]LF[/td][td][/td][/tr]
[/table]
You seem to have extraneous chars in your input as well as output data (a character whose code is 160). For this reason,
SUMPRODUCT is used in what follows instead of SUMIFS, because SUBSTITUTE is needed.
In B13 enter and copy down:
Rich (BB code):=SUMPRODUCT(INDEX($B$2:$D$10,0,MATCH($C13,$B$1:$D$1,0)), --(SUBSTITUTE($A$2:$A$10,CHAR(160),"")=SUBSTITUTE($A13,CHAR(160),"")))
In C13 control+shift+enter, not just enter, and copy down:
Rich (BB code):=LOOKUP(9.99999999999999E+307,INDEX($B$2:$D$10,MATCH(SUBSTITUTE($A13,CHAR(160),""), SUBSTITUTE($A$2:$A$10,CHAR(160),""),0),0),$B$1:$D$1)
Thank You Aladin...!!
FYI....
The data imported from Autocad which we see here as A1:D10, could be up to over hundred items or more!!. Therefore, I was planning to dedicate these items to their own sheet... Such as "Autocad Import!" as the first worksheet. And the 2nd worksheet named "Workup Summary!" for the final output.. This will change your formulas...??
Thanks Michael
If you replace the current input form which the current output is created, the output will change accordingly.