SOLVED

summing cells with $ sign

%3CLINGO-SUB%20id%3D%22lingo-sub-1590659%22%20slang%3D%22en-US%22%3Esumming%20cells%20with%20%24%20sign%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1590659%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20problems%20using%20the%20normal%20formula%20for%20adding.%26nbsp%3B%20(ex.%20%3DO3%2BG3%20)%26nbsp%3B%20if%20I%20have%20%24%20signs%20in%20the%20mentioned%20cells.%26nbsp%3B%20Is%20there%20a%20walk%20around%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1590659%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1590670%22%20slang%3D%22en-US%22%3ERe%3A%20summing%20cells%20with%20%24%20sign%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1590670%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F760562%22%20target%3D%22_blank%22%3E%40chewbaka%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIdeally%20the%20cells%20should%20contain%20numbers%20such%20as%2037%20or%20123.45%2C%20and%20their%20number%20format%20should%20be%20set%20to%20a%20currency%20format%20with%20%24.%20That%20way%2C%20you%20can%20simply%20sum%20the%20values.%3C%2FP%3E%0A%3CP%3EBut%20if%20the%20cells%20contain%20text%20values%20that%20start%20with%20%24%2C%20you%20can%20use%20a%20formula%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUBSTITUTE(G3%2C%22%24%22%2C%22%22)%2BSUBSTITUTE(O3%2C%22%24%22%2C%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormat%20the%20cell%20with%20the%20formula%20as%20currency.%20Or%20if%20you%20prefer%20the%20result%20as%20a%20text%20value%20again%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3DTEXT(SUBSTITUTE(G3%2C%22%24%22%2C%22%22)%2BSUBSTITUTE(O3%2C%22%24%22%2C%22%22)%2C%22%24%20%23%2C%23%230.00%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1590672%22%20slang%3D%22en-US%22%3ERe%3A%20summing%20cells%20with%20%24%20sign%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1590672%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20Wow%20what%20a%20quick%20reply..%20the%20substitute%20formula%20did%20work%20out%20fine.%26nbsp%3B%20Thank%20you%20very%20much.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1590678%22%20slang%3D%22de-DE%22%3ESubject%3A%20summing%20cells%20with%20%C3%A9%20sign%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1590678%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F760562%22%20target%3D%22_blank%22%3E%40chewbaka%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EIt%20would%20be%20of%20great%20advantage%20if%20you%20insert%20a%20file%20(without%20sensitive%20data)%20so%20that%20we%20can%20see%20and%20understand%20the%20problem%20better.%3C%2FSPAN%3E%20%3CSPAN%3ESo%20you%20can%20be%20helped%20faster%20and%20more%20precisely.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSPAN%20class%3D%22%22%3EAs%20described%2C%20the%20problem%20could%20be%20with%20the%20cell%20formatting%20that%20cells%20may%20have%20different%20formatting.%3C%2FSPAN%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CH1%20id%3D%22toc-hId-503319226%22%20id%3D%22toc-hId-503319226%22%3E%3CFONT%20size%3D%224%22%3EHow%20to%20control%20and%20understand%20settings%20in%20the%20Format%20Cells%20dialog%20box%20in%20Excel%3C%2FFONT%3E%3C%2FH1%3E%3CP%3E%3CFONT%20size%3D%224%22%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Ftroubleshoot%2Fexcel%2Fformat-cells-settings%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Ftroubleshoot%2Fexcel%2Fformat-cells-settings%3C%2FA%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20find%20this%20helpful%2C%20please%20mark%20it%20as%20%22Best%20Answer%22%20and%20as%20Like%20(click%20thumbs%20up)%2C%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello,

 

I am having problems using the normal formula for adding.  (ex. =O3+G3 )  if I have $ signs in the mentioned cells.  Is there a walk around?

 

Thanks.

3 Replies
Highlighted
Best Response confirmed by chewbaka (New Contributor)
Solution

@chewbaka 

Ideally the cells should contain numbers such as 37 or 123.45, and their number format should be set to a currency format with $. That way, you can simply sum the values.

But if the cells contain text values that start with $, you can use a formula like this:

 

=SUBSTITUTE(G3,"$","")+SUBSTITUTE(O3,"$","")

 

Format the cell with the formula as currency. Or if you prefer the result as a text value again:

 

 =TEXT(SUBSTITUTE(G3,"$","")+SUBSTITUTE(O3,"$",""),"$ #,##0.00")

Highlighted

@Hans Vogelaar  Wow what a quick reply.. the substitute formula did work out fine.  Thank you very much.

Highlighted

@chewbaka 

It would be of great advantage if you insert a file (without sensitive data) so that we can see and understand the problem better. thus you can also be helped faster and more precisely.

As described, the problem could be with the cell formatting that cells may have different formatting.

How to control and understand settings in the Format Cells dialog box in Excel

https://docs.microsoft.com/en-us/office/troubleshoot/excel/format-cells-settings

 

I would be happy to know if I could help.

 

If you find this helpful, please mark it as "Best Answer" and as Like (click thumbs up), it will be beneficial to more Community members reading here.

 

Nikolino

I know I don't know anything (Socrates)