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