How JustAnswer Works:

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

Ask Richard Your Own Question

Richard
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 31124
Experience:  Over 15 year experience resolving Microsoft Office Issues
32989067
Type Your Microsoft Office Question Here...
Richard is online now
A new question is answered every 9 seconds

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

Customer Question

Not able to sumif values "000", "00" and "0"
Submitted: 18 days ago.
Category: Microsoft Office
Expert:  Jess M. replied 18 days ago.

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

Customer: replied 18 days ago.
I want to sumif "000" based on the blocks below. Refer to the other sizes to see formula
Expert:  Jess M. replied 18 days ago.

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

Expert:  Jess M. replied 18 days ago.

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

Customer: replied 18 days ago.
I highlighted the red cells sorry.
Expert:  Jess M. replied 18 days 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?

Customer: replied 18 days 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: replied 18 days ago.
sorry G24
Expert:  Jess M. replied 18 days ago.

Ok. Please give me a moment to check

Customer: replied 18 days 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
Expert:  Jess M. replied 18 days ago.

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

Customer: replied 18 days ago.
ok no prob
Expert:  Jess M. replied 18 days ago.

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

Customer: replied 18 days ago.
correct. I would like to use the sumif formula or any short formula to sum the cells below.
Customer: replied 18 days ago.
I tried using the sumif formula but it does not want to recognize "000" "00" and "0" as a seperate function
Expert:  Jess M. replied 18 days 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

Expert:  The-PC-Guy replied 18 days ago.

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?

Customer: replied 18 days ago.
i'm trying to accomplish adding all units associated with "000" "00" "0" without have to add them all up individually.
Expert:  The-PC-Guy replied 18 days 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?

Customer: replied 18 days 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.
Expert:  The-PC-Guy replied 18 days 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?

Customer: replied 18 days 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: replied 18 days 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: replied 17 days ago.
hello can anyone help
Expert:  Richard replied 17 days ago.

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?

Expert:  Richard replied 17 days 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 ?

Customer: replied 17 days ago.
it's not possible to sumif "000"
Customer: replied 17 days 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?
Expert:  Richard replied 17 days 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.

Expert:  Richard replied 17 days 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