Excel Sum results Zero

[kashifshahzad]

Why the sum result is zero?

[BadlySpelledBuoy]

I suspect it is because your values are stored as text rather than numbers.
I cannot confirm this because I haven't opened the file.

Why not make life easier for those you are asking for help but attaching the file to the forum rather than a link to it?

BSB

[kashifshahzad]

sorry, i don't know how to attach a file here.

[kashifshahzad]

i have attached the file now.

[XLent]

As advised by BSB, your values are not numbers rather they are text (including currency code).

Assuming you cannot / do not wish to change format of your source values you will need to use an alternate to SUM, e.g.:

=SUMPRODUCT(REPLACE(B2:B20,1,4,"")+0)



returns 9,227,073.00

above assumes that currency code may deviate from PKR

[kashifshahzad]

Thanks for giving formula it worked. what if i don't want to use formula and change the format. i tried to change the format but still zero zero.

[XLent]

Sorry, by change the format I mean you would need to store as numbers, storing the ISO currency code in a different column...

If PKR is the only currency you have, and you have stored the numbers in Column B, you can technically apply a Custom Number Format of "PKR" #,##0, but I think that's of limited value. Generally speaking, store numbers as numbers, and keep any related text separate.

[kashifshahzad]

Thanks you very much