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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
341
Fluff
Fluff
D
  • Question Question
Xlookup or similar
Replies
6
Views
122
Peter_SSs
Peter_SSs
L
  • Question Question
Trouble splitting text to columns
Replies
6
Views
805
Peter_SSs
Peter_SSs
P
  • Solved
Counting number of cells with formula returning a value OR a #DIV/0 error?
Replies
3
Views
169
Eric W
Eric W
S
  • Question Question
Not sure of best formula - Trying IF statement with Xlookup
Replies
2
Views
247
Scott Huish
Scott Huish

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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