Returning #Value! in Cell

[Guest]

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

 

[Franz Verga]

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...

 

[Guest]

=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))

 

[Guest]

.. Why did you open a new thread?

Believe the OP just missed your response
in the other thread* by around 14 minutes <g>
*Re: Ignoring certain data

---

 

[Guest]

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

 

[Bob Phillips]

what is AP,BP and CP?

--
HTH

Bob Phillips

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

 

[Guest]

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)),"")

 

[Guest]

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,

 

[Bob Phillips]

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)

 

[Guest]

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)

---

 

[Guest]

oops, line:

(Click Tools > Options > Calculation mode - options there)

should read as:

(Click Tools > Options > Calculation tab - options there)

---