How do I make Excel stop treating blank cells as zero? Check out the following grid and my explanation, please.



Beginning Cost.........FinalCost ...........Savings
194............................................... ............ 194
286 .................................................. .........286
367 ................................315 ...................52


........................................Total Savings: 532


Column A is the initial cost, while Column B is the lower cost that i've negotiated. As negotiations are done, I'm updating column B with the "final cost." Column C, "Savings," simply computes the difference between column A and column B. At the bottom of Column C, I have a cell to compute the total savings using a basic "Sum" function. The problem is I want to keep a running tally of how much we'ved saved thus far and the fact that Excel is treating the blank cells as zero means that it's giving me inaccurate data. It appears that we've saved $532, when we've really only saved $52 thus far. I update Column B (Ending Cost) as that data becomes available. I know this should be a basic thing to do, but I've looked everywhere and haven't found the answer. Thanks in advance for your help!

Hi
it seems that the final cost column should contain the final cost. So, instead of an empty cell, B2 for instance should contain the same as A2 as long as you have no lower price. Savings would then correctly be 0

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thanks for replying, Pecoflyer. I phrased the scenario in a generic fashion for simplicity and brevity. In the real-world scenario (which is similar, but not identical to the one I portrayed), it wouldn't be feasible to put anything into Column B until we have received data back.

I see . Then try the following to extract the total savings : Adapt the ranges to your needs

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thanks a million, Charlie!! It worked! Do you have a link that explains how and why this works? That way, I'll know how to apply it to other situations. I don't recall seeing many of those characters in any of the other formulas I tried while researching this. Maybe that's why none of them worked. This works perfectly, because when '0' is entered, I want it to do that calculation (which it does!) and it ignores the blank cells (which it should!).

How does it know to differentiate between 'blank' and zero?

You're welcome, but my name is not Charlie. Have a look at http://www.xldynamic.com/source/xld....T.html#classic

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thanks a million!! That help-sheet makes sense and drives the point home. Sorry about the name thing. I saw your "handle" that says "je suis Charlie" (I am Charlie) and figured that was your name. Yeah, I know it didn't say, "Je m'appelle Charlie" (my name is Charlie). I guess I was a few days out of the loop on that being a slogan related to recent international events. Thanks again for everything, Herbert...LOL... Just Kidding... Thanks, Pecoflyer...

Thanks for providing your assistance and the tutorial. Just to check for understanding, I'd like to explain what I think is happening. This function is giving a summation of all the values in my "Savings" column whenever the corresponding figure in the "Final Cost" column has a value that is not blank. And you were able to identify the non-blank cells by putting in the constraint that it has to be <>"" (which means greater than or less than blank - since there's nothing inside the quotes). Correct, or flawed? That was a crafty way to solve it. I was focused on a way to adjust the subtraction problem (Column A minus Column B), but you illuminated a different approach.

To test my understanding, I tried replicating it using the following formula, but it gives me an error. Any clue why?

=SUMIF(b2:b4,<>"", c2:c4)

What sumproduct does is give a sum of all the products of values in one range multiplied with those in another (or more even). For your example, it is effectively summing (0*194), (0*286), and (1*52).

Sumproduct is great at extracting data from a table based on multiple criteria. Say you have a table with EntryDate, Customer, Type, Amount and you want all the total amount of the records for 1/5/14 for XYZ Inc. that are credits:

=sumproduct(EntryDate=Datevalue("1/5/14")*(Customer="XYZ Corp.)*(Type="C"),(Amount))

Your equation will work if written as:

=SUMIF(b2:b4,">0", c2:c4)

The criteria has to be a string.

Np with Charlie ( for me, allas not for those who were slaughtered). And yes, you are right to use the SUMIF function. I always seem to forget that it exists

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thanks to both of you!! I had been researching for a week before I joined this site, but now I know why I couldn't find anything: because the SUMPRODUCT used in this fashion is a bit of a workaround. I tried the SUMIF just to test my understanding and apply a similar function, but the SUMPRODUCT fits the bill in this case because it treats a blank cell differently than a cell that has zero. In my real-life scenario, there is a difference because a blank cell should be ignored (as I have not received data for those), whereas a cell with zero should be computed because that's the true value. So because of you two, I have two additional tools in my belt to use for future spreadsheets!!!