Change value in cell if cell value is zero, through another sheet.

[danpo]

Hello!

Not the most selfexplanatory heading, so here goes a more detailed problem defintion.

I have recieved a standard document, from which I'll have to extract vital data in form of numbers (costs). I do this by downloading the file and copy it to a template I've created. Through this template I sort the data in the document by article codes, specified in column A in the source document, and extract the corresponding values from the M column in the desired intervalls. Everything in the deisred spans of article codes is then summed up and shown in cells in the adjacent sheets. This is basically done with a "complex" PRODUCTSUM, looking like this:

=PRODUKTSUM((Sheet1!$A$9:$A$2004>=2140)*(sheet1!$A$9:$A$2004<=2149);(Sheet1!$M$9:$M$2004))

The above formula works from article numbers 2140 to 2149 and extract values from the column M. To be noted is that, even though slightly translated, the formatting is in swedish, with slight differences from the english.

This has worked perfectly so far but when I want to make a sum of all values, spanning from 0000 - 9999, excel somehow ignore values with article code 0000 (or just "0") as excel displays it.

As I have tried to work around this without finding a solution I can create by myself, I turn to you and hope you have the experience needed.

As I would prefer if no changes is made in the source file, I wonder if it is possible to automaticlly change the 0 values in column A to something else, say 11111 just by using the adjacent sheet.

If you have another solution to my problem, everything is deeply welcome!

Thanks in advance!

[NBVC]

Are the 0000 numbers entered as 0 and formatted as custom 0000?

If so, the SUMPRODUCT() or PRODUKTSUM() should work if you say >=0.

Can you attach a sample file showing how it does not work?