Maintaining number format with combining of cells

[punit83]

Hello !

I have a excel sheet with 3 column data

1st column = has sr no.
2nd column = has no. of unit
3rd coloum = has individual total amount value

Eg:-

#	Unit	TOTAL AMT
1	100	100.00
2	200	400.00
3	300	900.00
4	400	1,600.00
5	500	2,500.00
6	600	3,600.00
7	700	4,900.00
8	800	6,400.00
9	900	8,100.00
10	1,000	10,000.00

<tbody>
</tbody>

Total Count	10
Total Unit	5,500.00
Total Amount	38,500.00

<tbody>
</tbody>

I want all these 3 details in one cell with maintaining number format.

i am using these formula
="No : "&SUBTOTAL(3,Tablex[Unit])&" | "&"Unit : "&SUBTOTAL(9,Tablex[Unit])&" | "&"Total Amt : "&SUBTOTAL(9,Tablex[TOTAL AMT])

and result is like this

No : 10 | Unit : 5500 | Total Amt : 38500

<tbody>
</tbody>

( No comma separator)



(i am newbie so maybe there will be mistake in formula or even if anyone has easier formula than it will help me a lot)


Thanks in advance.

 

[FormR]

Hi, welcome to the forum!

You can try this:

="No : "&SUBTOTAL(3,Tablex[Unit])&" | "&"Unit : "&TEXT(SUBTOTAL(9,Tablex[Unit]),"#,##0")&" | "&"Total Amt : "&TEXT(SUBTOTAL(9,Tablex[TOTAL AMT]),"#,##0.00")

 

[punit83]

Thank you so muchhhhhhhh......... solved

 

[punit83]

Hello FormR,

i have new question please guide me.

I have this series


0.18 - 0.22
0.23 - 0.29
0.30 - 0.39
0.40 - 0.49
0.50 - 0.69
0.70 - 0.89
0.90 - 0.99
1.00 - 1.49
1.50 - 1.99

now for example i want to use formula that as i typed number in a1=any random number from 0.30 to 39 than result in b1 should be 0.30-0.39

is there any formula without vba ?


thanks in advance.