• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how sum figure of Indian format falue

roypabitra

roypabitra

Member
I have rupformat function and i convert number by rupformat like =rupformat(12345678) and now I want to sum three like this valu but its result shows #value


Rs.12,54,875.00

Rs.24,58,769.00

Rs.3,96,54,781.00


#VALUE!


please help me how I solve this problem
 
It would have helped to know what your custom function was doing. It sounds like it's creating a text output, rather than a value. This is causing your SUMS to mess up. Might be worth reading this article by Hui for how to format to Indian currency w/o messing up the values in the cell:

http://chandoo.org/wp/2010/07/26/indian-currency-format-excel/
 
Hi Pabitra ,


The problem is the commas ; if you remove the commas and sum , then you will get the correct result.


=SUBSTITUTE(rupformat(12345678),",","")


will remove the commas introduced by the rupformat function , and allow you to do arithmetic with the figures.


Narayan
 
Roypabitra


Firstly, Welcome to the Chandoo.org forums


As Luke and Narayan have said you are best to keep numbers as numbers

It sounds like your function =rupformat() is converting the numbers to text, as you've found out you then loose the functionality that numbers offer.


The link Luke posted explains how to format numbers in Indian Ruppee format
 
Respected Sir,


I have two three column in a data sheet like the following


TBA STAX TOTAL

100 2 103

200 0 200

300 10 310

400 0 400

100 5 105


Now sir i want to sum of TBA only those column on which Stax not equal to Zero.


Please Help me for the above
 
RoyPabitra

Try:

=SUMIFS(A2:A6,B2:B6,"<>0")

or

=SUMPRODUCT((B2:B6<>0)*(A2:A6))

or

=SUMIF(B2:B6,"<>0",A2:A6)

or

=SUM(IF(B2:B6<>0,A2:A6,)) Ctrl+Shift+Enter
 
RoyPabitra


Can you please create new posts for new questions even if it relates to the same data


You have posted 4 questions in 2 posts


This makes searching for solutions difficult


It may also potentially slow down your responses, as some of us may not look at a post which we know someone else is dealing with


We look forward to your assistance
 
You must log in or register to reply here.
Back
Top