Stumped... Need Help with formula...

Mykro · May 3, 2018

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

jtakw · May 3, 2018

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

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.

Aladin Akyurek · May 4, 2018

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

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"))

Mykro · May 4, 2018

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"))

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

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

Thanks
Michael

Aladin Akyurek · May 4, 2018

Mykro said:
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.

Mykro · May 4, 2018

Aladin Akyurek said:
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

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

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

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

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

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)

Aladin Akyurek · May 5, 2018

Row\Col	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
11
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

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)

Mykro · May 5, 2018

Aladin Akyurek said:
Row\Col
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
11
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

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

Aladin Akyurek · May 5, 2018

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

If you replace the current input form which the current output is created, the output will change accordingly.

Mykro · May 5, 2018

Aladin Akyurek said:
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

Stumped... Need Help with formula...

Mykro

Active Member

Excel Facts

jtakw

Well-known Member

Aladin Akyurek

MrExcel MVP

Mykro

Active Member

Aladin Akyurek

MrExcel MVP

Mykro

Active Member

Aladin Akyurek

MrExcel MVP

Mykro

Active Member

Aladin Akyurek

MrExcel MVP

Mykro

Active Member

Similar threads

Forum statistics

Share this page

Stumped... Need Help with formula...

Active Member

Excel Facts

Well-known Member

MrExcel MVP

Active Member

MrExcel MVP

Active Member

MrExcel MVP

Active Member

MrExcel MVP

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock