Sum Formula

[Mohammed Sadiq]

Could you please help me to get the sum result in each row as given below.

[pre]

QTY	RATE (BD)	TOTAL AMOUNT

134 SQM	120 SQM	         (134*120)
66 SQM	120 SQM	          (66*120)
25 SQM	120 SQM
3 Nos.	100 per number	

56 SQM	135 SQM
31 SQM	135 SQM	

40 SQM	45 SQM
3 SET	100 SET	

2530 SQM	10 SET
1745 SQM	10 SET
235 SQM	10 SET	

1370 SQM		

80 LM	40 LM

[/pre]

 

[oldchippy]

Hi,


Welcome to the forum, if you have 134 in cell A2 and 120 in B2 then it is


=A2*B2

 

[Mohammed Sadiq]

Dear Friend,


thanks for help.


I have asked in cell a1 contains (134SQM) and cell a2 contains (120SQM) then how to do the multiplication. This numbers can change in digit and the unit of measurement also changes in digit (SQM = 3 digits, LM=2 digit, Nos. = 4 digit)


Kindly help.

 

[Hui]

Mohammed


Firstly, Welcome to the Chandoo.org forums


It is bad practice to mix numbers and units like you have

You should try and keep the numbers and units in seperate cells


You can use the following formula

=LEFT(A1,FIND("SQM",A1)-1)*LEFT(A2,FIND("SQM",A2)-1)


You should look at using Custom Number Formats

where A1 will contain 134

Then apply a custom number format of #" SQM"

This way the cell will display 134 SQM

but you can then use a formula of =A1+A2

 

[Mohammed Sadiq]

Thank You Hui.


It's really works. Good Job Done.

 

[shrivallabha]

Alternatively you can use Custom Formatting for these cells where you need to display SQM by using:

1. Select cells where you need this formatting.

2. Choose "Format Cells" [CTRL + 1]

3. Choose "Custom" Category

4. Type : General" SQM"

The cells will now display SQM. See here for detailed explanation:

http://chandoo.org/wp/2008/02/25/custom-cell-formatting-in-excel-few-tips-tricks/


The advantage will be you can do the mathematical operations as they are.i.e. oldchippy's formula will work as it is.

 

[Hui]

Shrivallabha


As per the second part of my post

 

[shrivallabha]

Oops, Hui


Didn't see it....!