....(LEFT(Original!C2:C9101&" ",5)="pipe ")

Biff wrote:
Yeah, you're right!

But what if some cells only contain the the word pipe and don't have a
trailing space as in:

........LEFT(Original!C2:C9101,5)="pipe ")

Will work if:

pipe fitter

Won't work if:

pipe

Biff

"duane" wrote in message
...

does he not need this to eliminate the cell starting with with pipet?

=SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,5)="pipe
")*(Original!H2:H9101))

"Biff" wrote:


Hi!

Try this:

=SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,4)="pipe")*(Original!H2:H9101))

Biff

"Mosqui" wrote in message
...

Thanks very much for all the answers, I went through the posts and
learnt
a
lot, but I could find exactly what I need.
The problem I've got is that one of my criterias is part of the cell.
So, to be clear; I need all the cells which start with the word "pipe".
pipe 100 w
sp-pipe
pipet 333

From these three I just need to get the first one.
=SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*NOT(ISERROR((SEARCH ("pipe",Original!C2:C9101))))*(Original!H2:H910 1))

This is what I'm using at the moment.

Thanks in advance, and again congratulations to all the people who
colaborate with these answers.

Martin







--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.