Returning #Value! in Cell
 

Hello,

I am using the below formula:

=SUMPRODUCT((SUBSTITUTE(A1:A10,"P","")="N001")*(B1 :B10=--"02/06/06")*(C1:C10))

but am getting #VALUE! returned in the cell as I am trying to return Text
not numbers from the Column C cells. Does anyone know a way round this so I
can return the text?

Thanks,
Ross

Returning #Value! in Cell
 

Nel post
*Ross* ha scritto:

Hello,

I am using the below formula:

=SUMPRODUCT((SUBSTITUTE(A1:A10,"P","")="N001")*(B1 :B10=--"02/06/06")*(C1:C10))

but am getting #VALUE! returned in the cell as I am trying to return
Text not numbers from the Column C cells. Does anyone know a way
round this so I can return the text?

Thanks,
Ross

Why did you open a new thread?

Please continue in the original thread...


--
Ciao

Franz Verga from Italy

Returning #Value! in Cell
 

=SUMPRODUCT((SUBSTITUTE(A1:A10,"P","")="N001")*(B1 :B10=--"02/06/06")*(C1:C10))

Perhaps one alternative,
try array-entered (i.e. press CTRL+SHIFT+ENTER):
=INDEX(C1:C10,MATCH(1,(SUBSTITUTE(A1:A10,"P","")=" N001")*(B1:B10=--"02/06/06"),0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ross" wrote:

Hello,

I am using the below formula:



but am getting #VALUE! returned in the cell as I am trying to return Text
not numbers from the Column C cells. Does anyone know a way round this so I
can return the text?

Thanks,
Ross

Returning #Value! in Cell
 

"Franz Verga" wrote:
.. Why did you open a new thread?

Believe the OP just missed your response
in the other thread* by around 14 minutes <g
* Ignoring certain data
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Returning #Value! in Cell
 

I cnat get this to work I'm afraid, is there a formula I could write in
another cell that for example says if cell is AP = 1 or if cell is BP=2 or if
cell is CP =3 etc, then use the sum product formula this way?

Thanks

"Max" wrote:


=SUMPRODUCT((SUBSTITUTE(A1:A10,"P","")="N001")*(B1 :B10=--"02/06/06")*(C1:C10))

Perhaps one alternative,
try array-entered (i.e. press CTRL+SHIFT+ENTER):
=INDEX(C1:C10,MATCH(1,(SUBSTITUTE(A1:A10,"P","")=" N001")*(B1:B10=--"02/06/06"),0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ross" wrote:

Hello,

I am using the below formula:



but am getting #VALUE! returned in the cell as I am trying to return Text
not numbers from the Column C cells. Does anyone know a way round this so I
can return the text?

Thanks,
Ross

Returning #Value! in Cell
 

what is AP,BP and CP?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ross" wrote in message
...
I cnat get this to work I'm afraid, is there a formula I could write in
another cell that for example says if cell is AP = 1 or if cell is BP=2 or
if
cell is CP =3 etc, then use the sum product formula this way?

Thanks

"Max" wrote:



=SUMPRODUCT((SUBSTITUTE(A1:A10,"P","")="N001")*(B1 :B10=--"02/06/06")*(C1:C10
))

Perhaps one alternative,
try array-entered (i.e. press CTRL+SHIFT+ENTER):

=INDEX(C1:C10,MATCH(1,(SUBSTITUTE(A1:A10,"P","")=" N001")*(B1:B10=--"02/06/06
"),0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ross" wrote:

Hello,

I am using the below formula:



but am getting #VALUE! returned in the cell as I am trying to return
Text
not numbers from the Column C cells. Does anyone know a way round
this so I
can return the text?

Thanks,
Ross

Returning #Value! in Cell
 

They are names of lines I've tried using and IF statement but getting
limited success as its far from right

=IF(AND(C1="HP",1)(OR(C1="LP",2),(C1="Test",3)),"" )


"Bob Phillips" wrote:

what is AP,BP and CP?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ross" wrote in message
...
I cnat get this to work I'm afraid, is there a formula I could write in
another cell that for example says if cell is AP = 1 or if cell is BP=2 or
if
cell is CP =3 etc, then use the sum product formula this way?

Thanks

"Max" wrote:



=SUMPRODUCT((SUBSTITUTE(A1:A10,"P","")="N001")*(B1 :B10=--"02/06/06")*(C1:C10
))

Perhaps one alternative,
try array-entered (i.e. press CTRL+SHIFT+ENTER):

=INDEX(C1:C10,MATCH(1,(SUBSTITUTE(A1:A10,"P","")=" N001")*(B1:B10=--"02/06/06
"),0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ross" wrote:

Hello,

I am using the below formula:



but am getting #VALUE! returned in the cell as I am trying to return
Text
not numbers from the Column C cells. Does anyone know a way round
this so I
can return the text?

Thanks,
Ross

Returning #Value! in Cell
 

I've got this array formula to work but it appears to be slowing down
sometimes crashing my programme because I just have so much data that I am
using it on.

Thats why I am trying to use an if statement similar to the below:
=IF(AND(C1="HP",1)(OR(C1="LP",2),(C1="Test",3)),"" )

this or something similar am hoping will retuun numbers relating to the
text in the cell then I will use a separate sumproduct formula

if (F1 cell is AP = 1) or if (F1 cell is BP=2) or (if F1
cell is CP =3) etc

Thanks,



"Max" wrote:


=SUMPRODUCT((SUBSTITUTE(A1:A10,"P","")="N001")*(B1 :B10=--"02/06/06")*(C1:C10))

Perhaps one alternative,
try array-entered (i.e. press CTRL+SHIFT+ENTER):
=INDEX(C1:C10,MATCH(1,(SUBSTITUTE(A1:A10,"P","")=" N001")*(B1:B10=--"02/06/06"),0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ross" wrote:

Hello,

I am using the below formula:



but am getting #VALUE! returned in the cell as I am trying to return Text
not numbers from the Column C cells. Does anyone know a way round this so I
can return the text?

Thanks,
Ross

Returning #Value! in Cell
 

Don't understand. Do you want to check C1 for equal to HP or equal to 1? The
phrase '... names of lines ... ' means nothing to me.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ross" wrote in message
...
They are names of lines I've tried using and IF statement but getting
limited success as its far from right

=IF(AND(C1="HP",1)(OR(C1="LP",2),(C1="Test",3)),"" )


"Bob Phillips" wrote:

what is AP,BP and CP?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ross" wrote in message
...
I cnat get this to work I'm afraid, is there a formula I could write
in
another cell that for example says if cell is AP = 1 or if cell is
BP=2 or
if
cell is CP =3 etc, then use the sum product formula this way?

Thanks

"Max" wrote:




=SUMPRODUCT((SUBSTITUTE(A1:A10,"P","")="N001")*(B1 :B10=--"02/06/06")*(C1:C10
))

Perhaps one alternative,
try array-entered (i.e. press CTRL+SHIFT+ENTER):


=INDEX(C1:C10,MATCH(1,(SUBSTITUTE(A1:A10,"P","")=" N001")*(B1:B10=--"02/06/06
"),0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ross" wrote:

Hello,

I am using the below formula:



but am getting #VALUE! returned in the cell as I am trying to
return
Text
not numbers from the Column C cells. Does anyone know a way round
this so I
can return the text?

Thanks,
Ross

Returning #Value! in Cell
 

"Ross" wrote:
I've got this array formula to work but it appears to be slowing down
sometimes crashing my programme because I just have so much data that I am
using it on.

I'd try setting the calc mode to manual
(Click Tools Options Calculation mode - options there)
Then press F9 when all is ready to re-calc

This way, I can go about updating here and there in Excel
w/o recalc getting in the way until I'm ready to recalc
(I'd usually take a short break when I press F9 <g)

Use the smallest range size sufficient to cover (in the array formula)
Note that the above also applies when using SUMPRODUCT to ease calc
performance.

(I've got no further suggestions on alternative formulas)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Returning #Value! in Cell
 

oops, line:
(Click Tools Options Calculation mode - options there)

should read as:
(Click Tools Options Calculation tab - options there)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---