Microsoft Office

Microsoft Office Questions? Ask a IT Expert for Support ASAP

Ask a Computer Expert,
Get an Answer ASAP!

Microsoft Office

Not able to sumif values "000", "00" and "0"

Customer Question
Not able to sumif values...
Not able to sumif values "000", "00" and "0"
Submitted: 1 year ago.Category: Microsoft Office
Show More
Show Less
Ask Your Own Microsoft Office Question
Answered in 6 minutes by:
11/16/2016
Microsoft Office Technician: Jess M., Computer Support Specialist replied 1 year ago
Jess M.
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 7,247
Experience: Computer Software Specialist for more than 10 years
Verified

Hi, welcome and thank you for your question. My name is ***** ***** I am glad to assist you today.

I am very sorry about your issues. Can you please send me a sample of your Excel file data so that I can check from my end to see what causes the problem? You can use the Add Files button to attach a sample file in your reply.
Please let me know by replying to me here so that I can help you further.

Best regards,
Jess

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
I want to sumif "000" based on the blocks below. Refer to the other sizes to see formula
Microsoft Office Technician: Jess M., Computer Support Specialist replied 1 year ago

Thank you for the sample file. Please give me a moment to check it

Ask Your Own Microsoft Office Question
Microsoft Office Technician: Jess M., Computer Support Specialist replied 1 year ago

Can you please tell me which specific cell or column are you referring to or where the SUMIF formula is located?

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
I highlighted the red cells sorry.
Microsoft Office Technician: Jess M., Computer Support Specialist replied 1 year ago

For instance, E4 where the SUMIF formula is stored, is showing the result as 14. Is it wrong? What is your expected result for E4 then?

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
let's start with G14. My expected results is 0 but if I use the sumif version it gives me 5191 SUMIFS($G$138:$G$99916,$F$138:$F$99916,"000")
Customer reply replied 1 year ago
sorry G24
Microsoft Office Technician: Jess M., Computer Support Specialist replied 1 year ago

Ok. Please give me a moment to check

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
E24 COUNTIFS($F$138:$F$100000,"000",$D$138:$D$100000,"1",$E$138:$E$100000,"<=4")..... The answer should be 0 because I only want to count "000" if column D has a 1 in the below cells. If it helps to talk, I'm open to phone conversation
Microsoft Office Technician: Jess M., Computer Support Specialist replied 1 year ago

No, I do not provide phone support. I am checking your file now

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
ok no prob
Microsoft Office Technician: Jess M., Computer Support Specialist replied 1 year ago

You are saying G24 should be 0. It is now. But it is not using SUMIF formula.

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
correct. I would like to use the sumif formula or any short formula to sum the cells below.
Customer reply replied 1 year ago
I tried using the sumif formula but it does not want to recognize "000" "00" and "0" as a seperate function
Microsoft Office Technician: Jess M., Computer Support Specialist replied 1 year ago

I am very sorry, but I am running out of time and I cannot continue to help you with this. I am signing off now so please allow me to opt out so that my fellow experts who are available now can assist you further. You will be notified when another expert joins you here.

Thank you for using our service.

Best regards,

Jess

Ask Your Own Microsoft Office Question
Microsoft Office Technician: The-PC-Guy, Computer Manager replied 1 year ago
The-PC-Guy
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 2,084
Experience: 20 years experience providing remote computer support
Verified

if you are trying to sumif column D, if the value is 000 unfortunately that will not work. The numbers are stored as text and excel cannot sum text. It works the same for lookups, if numbers stored as text it cannot be looked up. If you convert to number then it would become a 0 which would be the same as other 0, so that might not work for you.

What exactly is your goal, what are you trying to accomplish?

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
i'm trying to accomplish adding all units associated with "000" "00" "0" without have to add them all up individually.
Microsoft Office Technician: The-PC-Guy, Computer Manager replied 1 year ago

so if it contains that value in column D. Inother words it is "000", "000a", "b000", "a000b","0000001" those would all get summed, and you are trying to sum column E?

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
but In Column E, I'm trying to countif not sumif "if they meet the criteria of having a 1 in column d". In column G....I'm trying to sumif based on Column E.
Microsoft Office Technician: The-PC-Guy, Computer Manager replied 1 year ago

ok, you will need to run me though this again.

you want to get both counts and sums. Which column do you want to get the counts or sums of based on the criteria?

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
I am trying to sumif Column G first. I would like to add all "000"(G24) units together based on the sizes in column E. Sum all "00" (G25)units and "0"(G26) units separately
Customer reply replied 1 year ago
To make it plain and simple. Refer to the calcs in all sizes excluding the leading 0 sizes. I'm trying to use the same calcs in the column but I can't figure out how to do it with 0000
Customer reply replied 1 year ago
hello can anyone help
Microsoft Office Technician: Richard, Software Specialist replied 1 year ago
Richard
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 41,027
Experience: Over 15 year experience resolving Microsoft Office Issues
Verified

Hi there, I am looking at this now for you.

But one thing, if you sumif on 0 you get 0 as its summing 0

Do you mean countif ?

To count how many times it appears?

Ask Your Own Microsoft Office Question
Microsoft Office Technician: Richard, Software Specialist replied 1 year ago

ok, I have looked at it but a little confused as you want to sumif on 0 and 000 ect but this will always return a 0

Or are you saying to sum the column next to it if it has 00 ?

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
it's not possible to sumif "000"
Customer reply replied 1 year ago
Column G......Look at the formulas by the size. I was trying to calc "000" the same way but you are right, it returns 0. Is there a way around this?
Microsoft Office Technician: Richard, Software Specialist replied 1 year ago

no its no possible as your summing 0

0+0 will always = 0

even 00000+00000 = 0

As your only adding 0

There is no way to get around this, it is not a limitation of Excel it is maths.

Ask Your Own Microsoft Office Question
Microsoft Office Technician: Richard, Software Specialist replied 1 year ago

If you would like any additional information or assistance, please do not hesitate to let me know.

And please take a second to rate my service by clicking one of the stars at the top of the screen then submit

Thank you

Ask Your Own Microsoft Office Question
Ask Richard Your Own Question
Richard
Richard
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 41,027
41,027 Satisfied Customers
Experience: Over 15 year experience resolving Microsoft Office Issues

Richard is online now

A new question is answered every 9 seconds

How JustAnswer works:

  • Ask an ExpertExperts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional AnswerVia email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction GuaranteeRate the answer you receive.

JustAnswer in the News:

Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.

What Customers are Saying:

My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed.

One Happy CustomerNew York

Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help.

Mary C.Freshfield, Liverpool, UK

This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!!

AlexLos Angeles, CA

Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult.

GPHesperia, CA

I couldn't be more satisfied! This is the site I will always come to when I need a second opinion.

JustinKernersville, NC

Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around.

EstherWoodstock, NY

Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know.

RobinElkton, Maryland

< Previous | Next >

Meet the Experts:

jstinehelfer

jstinehelfer

Information Systems Manager

36 satisfied customers

A+ Comptia Certified computer repair

JasonJames122

JasonJames122

Computer Enthusiast

0 satisfied customers

I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business

Jess M.

Jess M.

Computer Support Specialist

7,247 satisfied customers

Computer Software Specialist for more than 10 years

The-PC-Guy

The-PC-Guy

Computer Manager

2,084 satisfied customers

20 years experience providing remote computer support

James K.

James K.

Consultant

260 satisfied customers

Technical Director of IT Company

IT Miro

IT Miro

Computer Scientist

157 satisfied customers

Bachelor's Degree in Information Technology, Microsoft Certified Professional

Chris L.

Chris L.

Support Specialist

147 satisfied customers

Certified Expert with over 10 years experience.

< Previous | Next >

Related Microsoft Office Questions
There was an update today and since I did it I can't open
There was an update today and since I did it I can't open Word. There was one for PowerPoint and Excel and they are all fine … read more
bbao
bbao
Freelance IT Consultant
74 satisfied customers
I am finishing work on a large textbook and I find I cannot
I am finishing work on a large textbook and I find I cannot open my word documents.It says I do not have access to them. This is really frightening!! I need help. … read more
RussellA
RussellA
Solutions Architect
Bachelor\u0027s Degree
123 satisfied customers
I can't recover a really important document that I was
I can't recover a really important document that I was working on … read more
bbao
bbao
Freelance IT Consultant
74 satisfied customers
I'm having trouble with creating an automatic table of
I'm having trouble with creating an automatic table of contents in Word. I think it has something to do with formatting in the document. … read more
Kris R
Kris R
IT Manager
Computing
2,648 satisfied customers
When I type an e mail to a particular person, how do I
When I type an e mail to a particular person, how do I include multiple names recipients . … read more
Jess M.
Jess M.
Computer Support Specialist
7,247 satisfied customers
I have a document on one drive that wont open because the
I have a document on one drive that wont open because the file is corrupted. How can I open the document. It represents a weeks work. Would it help to use a different computer? … read more
Kris R
Kris R
IT Manager
Computing
2,648 satisfied customers
I have two cars a Hummer & Maserati I was able to access the
I have two cars a Hummer & Maserati I was able to access the Hummer, now I would like to use it on the Marz how do I do it? … read more
Kris R
Kris R
IT Manager
Computing
2,648 satisfied customers
I wrote a book and its now being published. There are many
I wrote a book and its now being published. There are many hard marks in the text which my publisher wants fixed. These occurred when i used space and enter when i shouldnt have. Iv corrected my inden… read more
George Sibiya
George Sibiya
PhD
216 satisfied customers
I was forced to reinstall office 2007 and now opening Excel,
I was forced to reinstall office 2007 and now opening Excel, Word, and PowerPoint I get the following: srdole32.tib with no comment as to what it is. … read more
bbao
bbao
Freelance IT Consultant
74 satisfied customers
I have been trying to activiate or reactivate Windows 10 for
I have been trying to activiate or reactivate Windows 10 for this year. I messed up in the beginning by putting in the wrong account name. Have not been able to recover from this. I have gotten a new … read more
Bryan
Bryan
IT Consultant
8,751 satisfied customers
I just downloaded outlook 2016 but cannot open it. I get the
I just downloaded outlook 2016 but cannot open it. I get the following message: "Cannot start Microsoft Outlook. Cannot open the outlook window. The set of folders cannot be opened. The outlook data f… read more
Viet - Computer Tech
Viet - Computer Tech
Computer Technican
Bachelor\u0027s Degree
953 satisfied customers
My laptop crashed with the last Windows 10 update. I had to
My laptop crashed with the last Windows 10 update. I had to buy a new laptop, but I need to recover my Microsoft 365 account. My contact information was on my old laptop. How do I recover my files? I … read more
Michelle
Michelle
Manager operations
Bachelor\u0027s Degree
1,275 satisfied customers
I had issues with slow indexing in my Outlook 2013. Went for
Hi there, I had issues with slow indexing in my Outlook 2013. Went for repair on line (after trying quick repair) - got a error message (crash) and all my Office 2013 package disappeared from my lapto… read more
Richard
Richard
Software Specialist
41,027 satisfied customers
We have Microsoft Word on our old computer under Wayne and
We have Microsoft Word on our old computer under Wayne and Beth Raasch Product ID 03***-***-****774-02692. When we tried to load it onto our new computer it asks for a 25 number long ID. This is all w… read more
Kris R
Kris R
IT Manager
Computing
2,648 satisfied customers
I can not get Outlook to contact my windstream server for
I can not get Outlook to contact my windstream server for e-mail. I get error messages. I phoned windstream. They could not resolve the problem and said phone you. The phone number did not answer. … read more
Jess M.
Jess M.
Computer Support Specialist
7,247 satisfied customers
My spreadsheet appears to be locked. It won' T let me input
My spreadsheet appears to be locked. It won' JA: What application are you using? Customer: t let me input numbers or alph JA: Have you installed any updates recently? Customer: Excel, no JA: Anything … read more
bbao
bbao
Freelance IT Consultant
74 satisfied customers
My email account has suddenly started asking me for a
My email account has suddenly started asking me for a password ***** when I key in what I know to be the correct passwords … read more
Moses C
Moses C
Bsc
335 satisfied customers
I need to retrieve my contacts and have forgotten my
I need to retrieve my contacts and have forgotten my password. … read more
Isaac Franklin
Isaac Franklin
Associate of Science in Social Sciences
971 satisfied customers
Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Show MoreShow Less

Ask Your Question

x