@@contextures And I'm not ashamed to say that I've watched it no fewer than three times since I keep encountering this same problem when I import a file from QuickBooks.
Thankyou so much. i asked my daughters and they showed me a hard way and took a longer time by deleting all the space in the number one by one . With your help i the surprised them with a simpler way in a jiff. Glad i found u.
I'm unable to rectify...excel Online....no paste special menu..but I DID get to use/understand the COUNTA and COUNT functions and what they are about...NICE to expand knowledge on Excel. Thank you for your simple explanation!
Thank you very much! This helped me a lot. I was wondering why the sum never made any sense. It's because I copied it from another cell. Dumb 'ol me. THANK YOU AGAIN!!!!!!!!!!!!!
THANK YOU! This is the ONLY video that actually solved my issue. Everything else just said "oh you must have made your equations manual instead of automatic"...NOPE! Sure didn't...and I have been ready to throw my computer out of the window for the last hour as I stare at the setting that is clearly marked "Automatic"
@Meow I think I marked all cells that I wanted to change, hit ctrl + f and in the upcoming window, went to the "replace" tab. Search for: [space] Replace with: [nothing] Edit: Obviously, "replace all" or go through them all with "replace"
I really really want to thank you for taking the time to make this so clear and helpful. Have to admit though that I have a massive spreadsheet (doing my expense to submit for tax...over 200 rows and 14 columns) and to fix each cell I want to cry. Might there be a faster way?
Thank you!!!!!!!!! This fixed my issue I was having. I was trying to subtract two numbers as an accounting format and kept getting 45.8499999999999999. I could not fix it and this totally fixed it!!! Thank you!!
I have a different issue, which is very infuriating and i hope you could help me out. So my issue is that Excel spits out a number for the =sum() formula but the number is higher than what it should be. I have physically added the numbers myself in a calculator several times and what i get on my calculator is correct, what excel adds up is off by about 5-10%. The way my spreadsheet is formulated is two columns are for each day of the month, so 2 columns per day. one column for item QTY and one column for total item price (which gets pulled from a main column of product prices all the way to the very left) x the qty. Now, i have no issues with excel adding the sum in each column for the total $ value for each day. My issue arises when i attempt to add the $ values for all days to get a total. So what i typical do is =sum( then i have to use CTRL to add each cell to the formula manually because every other cell is just a number for product QTY and not currency. This normally works when i'm selecting cells in the same row or column but for some reason it's not calculating correctly when i manually select every other cell rather than just dragging across a series of cells next to each other. I have checked to make sure each cell i'm adding is a currency and it is, even shows the $ sign. Yet, it's overshooting my numbers. What exactly am i doing wrong here?
Try SUMIF instead of SUM, and tell Excel to ignore columns where Qty is in the heading. For example, with headings in B1:O1, put this formula in P2, to get the total $ amounts in that row. Then copy the formula down to the rest of the rows: =SUMIF($B$1:$O$1,"*Qty*",$B2:$O2)
I am using sumif function but idk why its giving me 0 answer always btw i am putting correct parameters (range, criteria,sum range) everything is perfect then idk what i tried it on new excel sheet i trouble shooted but same problem ... I am using office 2019 with latest update could you help me please
@@contextures see table consists of sr. No., Date, hospital name, description and amount i.e 5 columns and rows were 25 So i put =sumif (whole table as RANGE, CRITERIA as hospital name as der were 5 hospitals , and SUM RANGE as amount column) Mam, if i have done mistake please correct me as i am beginner
Thank you for the video, but it didn't change my data. They look like a dates (there's no apostrophe and no extra spaces), but Excel isn't treating them as dates (no sum displayed at the bottom, only the cell count). I've also tried Text to Columns, format painter, changing the decimal sign, ensuring calculations option is set to automatic... Am at my wits end . (LibreOffice Calc is SO much better, but I can't use it with Zapier.) EDIT: I've solved it at last! In the Text to Columns wizard, I selected the date format 'MDY' instead of 'DMY' and FINALLY Excel recognised them as dates! 🤦♀ 😬 😵💫
This would drive me nuts every time it happened. Thank you for being a lifesaver.
You're welcome, relztrah! Thanks for letting me know that it helped you
@@contextures And I'm not ashamed to say that I've watched it no fewer than three times since I keep encountering this same problem when I import a file from QuickBooks.
This would drive me nuts every time it happened. Thank you very much for being a lifesaver.
You're welcome, Mohammad, and thanks for letting me know that the video helped you!
I was trying to fix this literally all night with =CLEAN, =TRIM, C/P as Text, nothing. Then your video saved my life. I cannot thank you enough!
Thankyou so much. i asked my daughters and they showed me a hard way and took a longer time by deleting all the space in the number one by one . With your help i the surprised them with a simpler way in a jiff. Glad i found u.
You're welcome! It was nice of your daughters to help you, and I'm glad you found this simpler way!
Thank you Contextures Inc.
It's a really helpful tip.
You're welcome, and thanks for letting me know that the video was helpful to you!
I'm unable to rectify...excel Online....no paste special menu..but I DID get to use/understand the COUNTA and COUNT functions and what they are about...NICE to expand knowledge on Excel. Thank you for your simple explanation!
Thanks a lot! Just came across this for the first time ever! :)
You're welcome, and thanks for your comment!
Oh my God, thank you so much much fo helping me. I'd got so stressfull of this.... may God bless you dearrrr
Thanks for this video. Just helped me troubleshoot a file someone else sent me.
You're welcome, Sharon, and thanks for letting me know that it helped you!
-Debra
This was such a life saver, WOW, So happy. Saved me a ton of work.
Thank you, Atiba, and I'm glad the video helped you!
Thank you! I couldn't figure this out and also took some time to find your video not knowing what to ask in search.
Thanks, Linda! I'm glad you finally found this video
Thank you very much! This helped me a lot. I was wondering why the sum never made any sense. It's because I copied it from another cell. Dumb 'ol me. THANK YOU AGAIN!!!!!!!!!!!!!
You're welcome, Mikoto, and thanks for letting me know that the video helped you fix that sum! We all do things like that occasionally!
Thank you for this quick solution!
You're welcome, Jelena, and thanks for letting me know that it helped you!
Thx for sharing this wonderful lesson. I was stuck, but this video helped me got out of the problem!
This solved my problem. Thank you very much!
You're welcome, Dashchariv, and thanks for your comment! I'm glad it solved your problem
I LOOVE YOU YOU SAVED ME HOURS OF CALCULATING BY HAND IN MY JOB
This was such a frustrating error, and your fix was perfect! Thanks.
THANK YOU! This is the ONLY video that actually solved my issue. Everything else just said "oh you must have made your equations manual instead of automatic"...NOPE! Sure didn't...and I have been ready to throw my computer out of the window for the last hour as I stare at the setting that is clearly marked "Automatic"
You're welcome, Lauren, and thanks for letting me know that the video helped you solve the problem!
Thank you, for a moment I was lost and had no clue until your little help.
I wish I knew this fix years ago. Thank you.
You're welcome, Garth, and at least you know this fix now!
THANK YOU SO MUCH!
For my problem, it was a "space" behind the number that I didn´t notice.
@Meow I think I marked all cells that I wanted to change, hit ctrl + f and in the upcoming window, went to the "replace" tab.
Search for: [space]
Replace with: [nothing]
Edit: Obviously, "replace all" or go through them all with "replace"
@@Aracnah Finally worked! thank you so much!!!
This was AMAZING! Game changer. Thank you so much!
It works. Thank you from Bangladesh.
You're welcome, and thanks for your comment!
Big problem resolved... Thanks alottttt
You're welcome, Amna, and thanks for your comment!
Thank you for teaching us! ❤❤❤
You're welcome, Kyra, and thanks for your comment!
Thank You! You saved my evening!
You're welcome, John, and thanks for leaving a comment!
Debra
I really really want to thank you for taking the time to make this so clear and helpful. Have to admit though that I have a massive spreadsheet (doing my expense to submit for tax...over 200 rows and 14 columns) and to fix each cell I want to cry. Might there be a faster way?
Thanks, Elizabeth, and at the 2:00 mark it shows how to fix all the selected cells at once. Make a backup copy of your workbook, then try that
-Debra
Wow, this problem was destroying me for so long but you fixed it so easily! Thank you so very much!
I solve my problem with this video . Thanks dear
You're welcome, Ahsan, and thanks for letting me know that it helped you
Great video for this common problem!
+ExcelIsFun Thank you!
Thanks for the tutorial!
You're welcome, and thanks for your comment!
Thank you!!!!!!!!! This fixed my issue I was having. I was trying to subtract two numbers as an accounting format and kept getting 45.8499999999999999. I could not fix it and this totally fixed it!!! Thank you!!
बहुत बहुत धन्यवाद ।। आपने हमारा बहुत समय बचाया ।। ईश्वर आपको स्वस्थ रखे ।।
Thank youuuuu so much. It's very helpful.
You're welcome, Rizka, and thanks for letting me know that it helped you! --Debra
Many many thanks to you Ma'am.
Life saver, thank you!!!
You're welcome, Susan, and thanks for letting me know that the video helped you!
Thank you very much .. you made my day I was trying to sort out this for last two days nothing worked but it did.. thanks a lot!!!!!!
Great it's work ! Thank you.
You're welcome, Ranjit, and thanks for your comment!
it didn’t work for me :( whyyy
Make sure you are entering the formulas exactly as the uploader does them.
If someone is still struggling even after watching this video, my advice is change the decimal sign - dot for comma or vice versa
Thanks for the helpful tip, Samuel!
A neat little tip, thanks for uploading this video.
Thanks for the help
You're welcome, Ashu, and thanks for your comment!
Thank you very much
You're welcome, and thanks for your comment!
Really helpful, thank you very much ❤️
You're welcome, Zandy, and thanks for your lovely comment!
-Debra
Amazing, loved it !!! thank you so much !!1 really
You're welcome, Zahir, and thanks for your comment!
Thank you so much... it solved my problem...
Thank you !
You're welcome, Simran, and thanks for your comment!
_Debra
Thanks. You are life saving.
I have a different issue, which is very infuriating and i hope you could help me out. So my issue is that Excel spits out a number for the =sum() formula but the number is higher than what it should be. I have physically added the numbers myself in a calculator several times and what i get on my calculator is correct, what excel adds up is off by about 5-10%.
The way my spreadsheet is formulated is two columns are for each day of the month, so 2 columns per day. one column for item QTY and one column for total item price (which gets pulled from a main column of product prices all the way to the very left) x the qty. Now, i have no issues with excel adding the sum in each column for the total $ value for each day. My issue arises when i attempt to add the $ values for all days to get a total.
So what i typical do is =sum( then i have to use CTRL to add each cell to the formula manually because every other cell is just a number for product QTY and not currency. This normally works when i'm selecting cells in the same row or column but for some reason it's not calculating correctly when i manually select every other cell rather than just dragging across a series of cells next to each other. I have checked to make sure each cell i'm adding is a currency and it is, even shows the $ sign. Yet, it's overshooting my numbers. What exactly am i doing wrong here?
Try SUMIF instead of SUM, and tell Excel to ignore columns where Qty is in the heading. For example, with headings in B1:O1, put this formula in P2, to get the total $ amounts in that row. Then copy the formula down to the rest of the rows:
=SUMIF($B$1:$O$1,"*Qty*",$B2:$O2)
Thanks Deborah... Paste Special Add -- great tip.
+krn14242 Thanks, glad you like the tip!
Thank you!!
You're welcome, and thanks for your comment!
Thank you. Solved my problem!
After that it’s show wrong formula like =() +0, how i remove this automatic +0, to get my actual formula?
Thank you. Worked like a charm.
You are a genius 👍🏻
This helped me greatly. Thank you !
Thank you so much. exactly what i required
Thanks for sharing
Great video..truly helped.
you sir deserves a hat
Wow , thank you so much this actually fixed my problem :)
Thank you for this very helfpul tip - saved me a ton of time!!
I am using sumif function but idk why its giving me 0 answer always btw i am putting correct parameters (range, criteria,sum range) everything is perfect then idk what i tried it on new excel sheet i trouble shooted but same problem ... I am using office 2019 with latest update could you help me please
If you do a SUM of those numbers, do you get the correct total? If not, they aren't real numbers, so use one of the steps to fix them.
@@contextures yes i do get correct sum.. problem is with sumif formula where i am getting 0 as answer
@@JayDhanavade What is your SUMIF formula, and what ranges have the criteria and amounts to sum?
@@contextures see table consists of sr. No., Date, hospital name, description and amount i.e 5 columns and rows were 25
So i put =sumif (whole table as RANGE, CRITERIA as hospital name as der were 5 hospitals , and SUM RANGE as amount column)
Mam, if i have done mistake please correct me as i am beginner
@@JayDhanavade Thanks, and the hospital name column should be the range, instead of the whole table
Thanks 🙏🏻
You’re welcome, Javid, and thanks for your comment!
Very helpful , thank you.
You're welcome, Aida! Thanks for letting me know that it helped
Thank you for the video, but it didn't change my data. They look like a dates (there's no apostrophe and no extra spaces), but Excel isn't treating them as dates (no sum displayed at the bottom, only the cell count). I've also tried Text to Columns, format painter, changing the decimal sign, ensuring calculations option is set to automatic... Am at my wits end . (LibreOffice Calc is SO much better, but I can't use it with Zapier.)
EDIT: I've solved it at last! In the Text to Columns wizard, I selected the date format 'MDY' instead of 'DMY' and FINALLY Excel recognised them as dates! 🤦♀ 😬 😵💫
Thank you for sharing your solution to the date problem! I'm sure that format tip will help other people too
thanks, worked for me!
very useful 👍 thanks
Thanks
very helpful, thank you!
THANK YOU VERY MUCH, SAVED THE DAY :-)
Awesome, it works! I thought that my Excel was out of date
Thanks, Emmanuel! I'm glad it worked for you
Thank you! very helpful and to the point.
Thanks for the video. It was driving me crazy !!!
You're welcome, Sergio, and thanks for leaving a comment!
Nice and clear. Thank you!
WOW! It really helped
You are an absolute LEGEND!
YOU JUST SAVED MY LIF
It does not work on my spreadsheet
Helpful
Thank you, Manoj!
Lifesaver indeed!!!!
Very helpful video Thanks alot dear.
Keep going good work :-)
Thank you, Feroz!
Excellent!
Mine still doesnt add up. I followed every step you hv shown but still 0. :/
Try copying the numbers, and paste as values in a different workbook. Do they add correctly there?
@@contextures no. Still zero :/
@@contextures something wrong with my excel i guess. Thankyou btw
THANK YOU!!!
Very Helpful
Thank you for letting me know that the video helped. I appreciate it!
I still can't add two data of two cells...pls help
Superb Thank you very much
Very helpful ..Thnx
Thanks!!!
ThanQ so much
thank you
It's didn't help m to clear my problem I tried it didn't work
I ve Tried to use =sum() with some formulated cells but it doesnt work, every time it gives 0 value , i need help
Do you have a comment in the cell? That was my problem. Once I cleared the comment, it worked perfectly.
Still not working for me :(
I can't get this to work on Google Sheets. It doesn't have the same pasting options. crycrycry
Thnk u 👍🏻🙏
You're welcome, and thanks for your comment!
Phenomenal thanks
Bless you
001 002 Bina error Ke Kaise Type Hua hai Jabki Upper left me green error Show hota Hai