Stumped... Need Help with formula...

M

Mykro

Active Member
Joined
Oct 17, 2002
Messages
337
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
 
Last edited:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Mykro said:
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
Click to expand...

Hi,

You can't use Range references like that in an IF/AND statement, perhaps if you explain in words what you're trying to do (and possibly show a small data sample), we can try to figure it out.
 
Last edited:
Upvote 0
Mykro said:
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
Click to expand...

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"))
 
Upvote 0
Aladin Akyurek said:
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"))
Click to expand...


Thank You Aladin..! Works Perfect...!

(I might have another one for you.. I'll post it back here if I do...)

Thanks
Michael
 
Upvote 0
Aladin Akyurek said:
You are very welcome. Do not hesitate to come back whenever in need or otherwise.
Click to expand...

Hello Aladin,

As I had mentioned in my last reply that your formula worked perfectly... But I overlooked something...

What I'n trying to accomplish is doing plan take-off using Autocad.. I'm Using the DATAEXTRACTION Command to extract my plan take-off to Excel...
This is the Data as it comes straight from AutoCad:



Excel 2012
ABCD
1DescriptionSFLFEA
26in A Curb6439.682201.1511
34in Sidewalk100.58591.7821
4Lgt Standard0.40114
54in Expansion Paper w_Chalk1.9311
64in Expansion Paper w_Chalk1.8881
74in Expansion Paper w_Chalk1.9741
84in Expansion Paper w_Chalk2.1801
94in Expansion Paper w_Chalk2.0461
104in Expansion Paper w_Chalk1.9431
Summary


This is the Data after I format it somewhat...


Excel 2012
ABCD
1DescriptionSFLFEA
26in Concrete Curb201.15
34in Concrete Sidewalk100.59
4Light Standard Foundation14
54in Expansion Paper w_Chalk1.931
64in Expansion Paper w_Chalk1.888
74in Expansion Paper w_Chalk1.974
84in Expansion Paper w_Chalk2.18
94in Expansion Paper w_Chalk2.046
104in Expansion Paper w_Chalk1.943
Summary


And here is what I'm trying to achieve... From here I do my pricing etc... But I'm not experienced enough with some of the more technical formulas.. Your MATCH formula can't sum the quantities where the Expansion Papers are listed... Notice how how the units are SF, LF, EA,(Square Foot, Lineal Foot, EAch) vs. How AutoCad exports them...


Excel 2012
ABC
12DescriptionQtyUnit
136in Concrete Curb201.15LF
144in Concrete Sidewalk100.59SF
15Light Standard Foundation14EA
164in Expansion Paper w_Chalk11.962LF
Summary
Cell Formulas
RangeFormula
B15=SUMIF($A:$A,A15,$D:$D)
B16=SUMIF($A:$A,A16,$C:$C)
 
Last edited:
Upvote 0
[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)
 
Upvote 0
Aladin Akyurek said:
[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)
Click to expand...

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
 
Upvote 0
Mykro said:
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
Click to expand...

If you replace the current input form which the current output is created, the output will change accordingly.
 
Upvote 0
Aladin Akyurek said:
If you replace the current input form which the current output is created, the output will change accordingly.
Click to expand...

Aladin,

I'm working on it now... I'll post it back here when I get it working..

I really appreciate your hard work..!! It's put me in the right direction.. Thank You..!!

Michael
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
List Ranking
Replies
6
Views
345
Fluff
Fluff
D
  • Question Question
Xlookup or similar
Replies
6
Views
183
Peter_SSs
Peter_SSs
K
  • Solved
Formula changes to Text when edited
Replies
3
Views
68
Joe4
Joe4
L
  • Question Question
Trouble splitting text to columns
Replies
6
Views
844
Peter_SSs
Peter_SSs
P
  • Solved
Counting number of cells with formula returning a value OR a #DIV/0 error?
Replies
3
Views
227
Eric W
Eric W

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top