Thread: sumproduct wildcats

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
>

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

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

yup...maybe this?

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

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

scratch that...brain cramp

"duane" wrote:

> yup...maybe this?
>
> =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C9101,4)="pipe")*(LEN(Original!C2:C9101)=4)*(Origin al!H2:H9101))
>
> "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
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >

and the answer is both

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

"duane" wrote:

> yup...maybe this?
>
> =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C9101,4)="pipe")*(LEN(Original!C2:C9101)=4)*(Origin al!H2:H9101))
>
> "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
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >

That will only count cells with a len of 4 chars!

Now, it will count

pipe

and nothing else!

Try this:

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

Also, if you ever need to calculate on just the specific word "pipe" you can
pad the beginning of each cell with a space:

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

OR

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

Variations of this method come in handy if you're doing "database like"
searches. It's much more reliable then using:

ISNUMBER(SEARCH(.....)

but I still haven't found a method that is 100% bullet proof!

Biff

"duane" <duane@discussions.microsoft.com> wrote in message
news:A19CF019-CCE2-4A80-A3D9-7524208DEE7D@microsoft.com...
> yup...maybe this?
>
> =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C9101,4)="pipe")*(LEN(Original!C2:C9101)=4)*(Origin al!H2:H9101))
>
> "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
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

Thanks for your help, that was great !!!!



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

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

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
>

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

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

yup...maybe this?

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

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

scratch that...brain cramp

"duane" wrote:

> yup...maybe this?
>
> =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C9101,4)="pipe")*(LEN(Original!C2:C9101)=4)*(Origin al!H2:H9101))
>
> "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
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >

and the answer is both

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

"duane" wrote:

> yup...maybe this?
>
> =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C9101,4)="pipe")*(LEN(Original!C2:C9101)=4)*(Origin al!H2:H9101))
>
> "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
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >