cells functioning odd
 

I have a huge excel sheet with 25000 lines it, i am using the following sum
if function: =SUM(IF(make!$A$2:$A$9571=finally!C1270,make!$C$2: $C$9571,0),0)
entered with Ctrl+Shift+Enter.

Here is an example of the problem:

PART# VALUE
A1. 025 B1. (THIS IS WHERE THE ABOVE FORMULA IS ENTERED)

The value in B1 does not want to display unless i go into A1 as if i were
editing it and then, once the cursor starts blinking i just simply push ENTER
to confirm the value in A1 and that is when the value in B1 appears. The
values in the a coloumn A were imported as text because the first digit is a
zero and if imported as general or as a number it looses the zero. I tried
formating afterwards the cell value from text to number and it still doesn't
work unless I manually go in and then push enter. There is so many parts it
would take me days to go into each cell and push enter.

Any help is appreciated.

Much thanks in advance!

With that many lines in it, it is probably in recalc constant mode
but try
=SUM(IF(value(trim(make!$A$2:$A$9571))=finally!C12 70,make!$C$2:$C$9571,0),0)
also what happens when you force a recalc (F9) it could be that you need
to go to <tools<options<Calulation and verify that calculation is set for
automatic.

if the leading zeros disappear when you inport, I assume the data is totally
numeric.
You might also try a helper column with
= value(trim(A1)) to reconvert to number.
"Dan" wrote:

I have a huge excel sheet with 25000 lines it, i am using the following sum
if function: =SUM(IF(make!$A$2:$A$9571=finally!C1270,make!$C$2: $C$9571,0),0)
entered with Ctrl+Shift+Enter.

Here is an example of the problem:

PART# VALUE
A1. 025 B1. (THIS IS WHERE THE ABOVE FORMULA IS ENTERED)

The value in B1 does not want to display unless i go into A1 as if i were
editing it and then, once the cursor starts blinking i just simply push ENTER
to confirm the value in A1 and that is when the value in B1 appears. The
values in the a coloumn A were imported as text because the first digit is a
zero and if imported as general or as a number it looses the zero. I tried
formating afterwards the cell value from text to number and it still doesn't
work unless I manually go in and then push enter. There is so many parts it
would take me days to go into each cell and push enter.

Any help is appreciated.

Much thanks in advance!

Now this doesn't help me at all because it looses the zeros, i need the zeros
there because 005 is unique format, because there is a 005 0005 and just 5,
so i have to able to tell which value it is.

"bj" wrote:

With that many lines in it, it is probably in recalc constant mode
but try
=SUM(IF(value(trim(make!$A$2:$A$9571))=finally!C12 70,make!$C$2:$C$9571,0),0)
also what happens when you force a recalc (F9) it could be that you need
to go to <tools<options<Calulation and verify that calculation is set for
automatic.

if the leading zeros disappear when you inport, I assume the data is totally
numeric.
You might also try a helper column with
= value(trim(A1)) to reconvert to number.
"Dan" wrote:

I have a huge excel sheet with 25000 lines it, i am using the following sum
if function: =SUM(IF(make!$A$2:$A$9571=finally!C1270,make!$C$2: $C$9571,0),0)
entered with Ctrl+Shift+Enter.

Here is an example of the problem:

PART# VALUE
A1. 025 B1. (THIS IS WHERE THE ABOVE FORMULA IS ENTERED)

The value in B1 does not want to display unless i go into A1 as if i were
editing it and then, once the cursor starts blinking i just simply push ENTER
to confirm the value in A1 and that is when the value in B1 appears. The
values in the a coloumn A were imported as text because the first digit is a
zero and if imported as general or as a number it looses the zero. I tried
formating afterwards the cell value from text to number and it still doesn't
work unless I manually go in and then push enter. There is so many parts it
would take me days to go into each cell and push enter.

Any help is appreciated.

Much thanks in advance!

Dan wrote...
Now this doesn't help me at all because it looses the zeros, i need the zeros
there because 005 is unique format, because there is a 005 0005 and just 5,
so i have to able to tell which value it is.
....
"Dan" wrote:
I have a huge excel sheet with 25000 lines it, i am using the following sum
if function: =SUM(IF(make!$A$2:$A$9571=finally!C1270,make!$C$2: $C$9571,0),0)
entered with Ctrl+Shift+Enter.
....

Replace the function above with

=SUMIF(make!$A$2:$A$9571,finally!C1270,make!$C$2:$ C$9571)

I assume the (finally!C1270) cell is also text formatted
I still don't know why you have your problem but
you can try the sumif Harlon recommended or
=sumproduct(--(make!$A$2:$A$9571=finally!C1270),make!$C$2:$C$957 1)

"Dan" wrote:

Now this doesn't help me at all because it looses the zeros, i need the zeros
there because 005 is unique format, because there is a 005 0005 and just 5,
so i have to able to tell which value it is.

"bj" wrote:

With that many lines in it, it is probably in recalc constant mode
but try
=SUM(IF(value(trim(make!$A$2:$A$9571))=finally!C12 70,make!$C$2:$C$9571,0),0)
also what happens when you force a recalc (F9) it could be that you need
to go to <tools<options<Calulation and verify that calculation is set for
automatic.

if the leading zeros disappear when you inport, I assume the data is totally
numeric.
You might also try a helper column with
= value(trim(A1)) to reconvert to number.
"Dan" wrote:

I have a huge excel sheet with 25000 lines it, i am using the following sum
if function: =SUM(IF(make!$A$2:$A$9571=finally!C1270,make!$C$2: $C$9571,0),0)
entered with Ctrl+Shift+Enter.

Here is an example of the problem:

PART# VALUE
A1. 025 B1. (THIS IS WHERE THE ABOVE FORMULA IS ENTERED)

The value in B1 does not want to display unless i go into A1 as if i were
editing it and then, once the cursor starts blinking i just simply push ENTER
to confirm the value in A1 and that is when the value in B1 appears. The
values in the a coloumn A were imported as text because the first digit is a
zero and if imported as general or as a number it looses the zero. I tried
formating afterwards the cell value from text to number and it still doesn't
work unless I manually go in and then push enter. There is so many parts it
would take me days to go into each cell and push enter.

Any help is appreciated.

Much thanks in advance!

Ok, I got it to work, the reason I wasn't getting a resul is that, when I
formatted my cells one was a GENERAL and other was TEXT, now that I converted
both to TEXT I get the correct result but the formula Harlan Grove gave does
not give the correct resul instead of the value "1" I get "5" but all other
formulas get the correct value.

Thanks Guys

"bj" wrote:

I assume the (finally!C1270) cell is also text formatted
I still don't know why you have your problem but
you can try the sumif Harlon recommended or
=sumproduct(--(make!$A$2:$A$9571=finally!C1270),make!$C$2:$C$957 1)

"Dan" wrote:

Now this doesn't help me at all because it looses the zeros, i need the zeros
there because 005 is unique format, because there is a 005 0005 and just 5,
so i have to able to tell which value it is.

"bj" wrote:

With that many lines in it, it is probably in recalc constant mode
but try
=SUM(IF(value(trim(make!$A$2:$A$9571))=finally!C12 70,make!$C$2:$C$9571,0),0)
also what happens when you force a recalc (F9) it could be that you need
to go to <tools<options<Calulation and verify that calculation is set for
automatic.

if the leading zeros disappear when you inport, I assume the data is totally
numeric.
You might also try a helper column with
= value(trim(A1)) to reconvert to number.
"Dan" wrote:

I have a huge excel sheet with 25000 lines it, i am using the following sum
if function: =SUM(IF(make!$A$2:$A$9571=finally!C1270,make!$C$2: $C$9571,0),0)
entered with Ctrl+Shift+Enter.

Here is an example of the problem:

PART# VALUE
A1. 025 B1. (THIS IS WHERE THE ABOVE FORMULA IS ENTERED)

The value in B1 does not want to display unless i go into A1 as if i were
editing it and then, once the cursor starts blinking i just simply push ENTER
to confirm the value in A1 and that is when the value in B1 appears. The
values in the a coloumn A were imported as text because the first digit is a
zero and if imported as general or as a number it looses the zero. I tried
formating afterwards the cell value from text to number and it still doesn't
work unless I manually go in and then push enter. There is so many parts it
would take me days to go into each cell and push enter.

Any help is appreciated.

Much thanks in advance!