....(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" <duane@discussions.microsoft.com> wrote in message
> news:5966F52E-A6FB-407C-BA7A-EFDB02FA9BD8@microsoft.com...
>
>>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:C9101,5)="pipe
>>")*(Original!H2:H9101))
>>
>>"Biff" wrote:
>>
>>
>>>Hi!
>>>
>>>Try this:
>>>
>>>=SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C9101,4)="pipe")*(Original!H2:H9101))
>>>
>>>Biff
>>>
>>>"Mosqui" <Mosqui@discussions.microsoft.com> wrote in message
>>>news:3AD773CE-A42D-470C-BF05-9F04D27977B3@microsoft.com...
>>>
>>>>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:H9101 ))
>>>>
>>>>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.