Getting Total of numeric values in Alhanumeric Numbers

[VDS]

Dear Respected Members,

I attach herewith the data with 6 different columns.

Column B gives the specification of container like 18 x 40". It means there are 18 containers with 40 Feet (size) and like that.

Different Columns have been defined for different feet and have separated data and taken grand total of containers with formula.

What I need the following: -

1. is the Formula "=IF(VALUE(LEFT(RIGHT(B5,3),2))=$D$2,VALUE(MID(B5,1,FIND("X",B5)-2)),"") can be simplified ?
2 Total should be extracted at the YELLOW MARKED places without Columns C,D E & F.

How it can be done ? help is requested.


VDS

 

[Mike H..]

Dear Respected Members,

I attach herewith the data with 6 different columns.

Column B gives the specification of container like 18 x 40". It means there are 18 containers with 40 Feet (size) and like that.

Different Columns have been defined for different feet and have separated data and taken grand total of containers with formula.

What I need the following: -

1. is the Formula "=IF(VALUE(LEFT(RIGHT(B5,3),2))=$D$2,VALUE(MID(B5,1,FIND("X",B5)-2)),"") can be simplified ?
2 Total should be extracted at the YELLOW MARKED places without Columns C,D E & F.

How it can be done ? help is requested.


VDS

Hi,

Have a look at your workbook, my formula are in Col C. The formula in C23 is this dragged down.

=SUMPRODUCT((--MID($B$3:$B$19,FIND("X",$B$3:$B$19)+1,3)=--MID(A23,FIND(" ",A23)+1,2))*(LEFT($B$3:$B$19,FIND(" ",$B$3:$B$19))))

 

[VDS]

@Hellow Mike,

Nice reply Thanks a lot


VDS