Stumped... Need Help with formula...

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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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

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
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"))
 
Upvote 0
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
 
Upvote 0
You are very welcome. Do not hesitate to come back whenever in need or otherwise.

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
[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
 
Upvote 0
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.
 
Upvote 0
If you replace the current input form which the current output is created, the output will change accordingly.

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

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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