How to calculate the sum ?

[Umesh Chahar]

Hi all,
i want to calculate the sum of this range , this range includes numbers and text.these are two different ranges which are having data in the form of GB's and TB's. sp please tell me how to sum it .
total
used
300GB
268GB
1648GB
1551GB
3582GB
3314GB
3417GB
2992GB
1457GB
1295GB
550GB
503GB
2972GB
2416GB
7414GB
6554GB
11TB
8221GB
3363GB
2689GB
341GB
278GB
93GB
78GB
1091GB
797GB
527GB
478GB
527GB
478GB

 

[Somendra Misra]

Hi Umesh,

Try below array formula:

=SUM(IF(ISNUMBER(--SUBSTITUTE($A$1:$A$30,"GB","")),--SUBSTITUTE($A$1:$A$30,"GB","")))+(SUM(IF(ISNUMBER(--SUBSTITUTE($A$1:$A$30,"TB","")),--SUBSTITUTE($A$1:$A$30,"TB","")))*1024)

Confirm with Ctrl+Shift+Enter.

Regards,

 

[Umesh Chahar]

thanks for sharing the formula its woking now.

 

[Somendra Misra]

You are Welcome!!!!

Regards,

 

[Khalid NGO]

Hi Umesh,
Other than Sir SM solution, you can try the following also:

suppose your data in column A, try this in B1
=SUBSTITUTE(SUBSTITUTE(A1,"GB",""),"TB","")+0

Copy down till A30, and apply =SUM(B1:B30)

1st substitute will remove GB and 2nd will remove TB, and +0 will convert the result in numbers.

Regards,

 

[Umesh Chahar]

Thanks sir its really awesome its working fine .thanks a lot

 

[Umesh Chahar]

But there is a problem sum data is in the form of GB AND TB and they are differnt units . so we cant add them , either we have to make them in to same unit that is either GB OR TB . then add

 

[Somendra Misra]

@Umesh Chahar

May be below formula is simple and does not involve a special key stroke (CSE), but does require a helper column.
Considering your data in A1:A30 put below formula in B1 and copy down than you can use =SUM(B1:B30).

For getting result in total GB:
=IF(ISNUMBER(SEARCH("TB",A1)),SUBSTITUTE(A1,"TB","")*1024,SUBSTITUTE(A1,"GB",""))

For getting total in TB:
Divide the sum by 1024.

Regards,

 

[Khalid NGO]

Umesh if you upload your file with same sample data it will help users to understand your actual requirement.

Regards,

 

[shrivallabha]

If your data is in two units GB and TB then you can try following formula also (normally entered)
=SUMPRODUCT(LEFT(A1:A30,LEN(A1:A30)-2)*POWER(1024,--(RIGHT(A1:A30,2)="TB")))

 

[Faseeh]

Hi,

you can also try this:

=SUMPRODUCT((TRANSPOSE(RIGHT(A1:A8,2))=({"KB";"MB";"GB";"TB"}))*TRANSPOSE(MID(A1:A8,1,LEN(A1:A8)-2))*({1;10;100;1000}))

With your data in A1:A8. You can reduce/criteria by changing {"KB";"MB";"GB";"TB"} and factors to be multiplied with
{1;10;100;1000} works only with two letters appended.

Use with Ctrl Shift Enter

Thanks.